Conditionally selecting a range of data

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,

Could you please lend me a hand with conditionally selecting ranges within 2 columns in a excel file using a macro?I am new to writing macros.

I have a file with 5 columns. one with date, time, latitude, longitude and amplitude.
I need to conditionally select data from the latitude and longitude columns and paste them into a new workbook.
The file has a total of 32001 rows, including the title rows.
The conditions that i need are the latitude values should be within -37.9382 and -32.004 and the longitude values should be within 136.7754 and 141.0698
Is there anyway that someone could guide me? :)
Extremely looking forward for a person's kind help,
Kind Regards,

6 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
Did you try autofilter and coping the filtered data in another location.

data-filter-autofilter
use custom under the arrows in the two relevant columns
if you are not able to get it post back
I do not understand what you are suggesting.
Could you please explain it to a beginner?
Thank you so much
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
Quote The conditions that i need are the latitude values should be within -37.9382 and -32.004 and the longitude values should be within 136.7754 and 141.0698
Unquote

TAKE A PRINTOUT OF THE FOLLOWING INSTRUCTIONS AND FOLLOW

-select your whole data including column headings
-click data(menu bar)-filter-autofilter
-you will get small arrows in all he column headings.
Click the latitude column arrow.
In the download list click "custom"
In the left small window click the arrow and choose
"Greater than equal to"
On right window type -37.9382
Choose "and" in the middle row
Bottom row left window
"less than or equal to"
On right window type -32.004
Click ok.
Some rows will be hidden and some visible

Again go to longitude column and click the arrow
Repeat the same action
Excep greater than eequal to value is 136.7754
And less than equal to value is 141.0698
Click ok

You will get only those rows for which latitude is between the two values you have given AND longitude between those two values

Highlight filtered rows and click edit- copy
Go to any other location and click edit -paste.
Not in the same data rows in the sheet but can be in another row below the data
(it can be any other sheet )

Now go back to the data sheet and again click data-filter-autofiltger
So that autofilter mode is removed.

If any problem post back with detailed steps which gave your problem

STUDY EXCEL help under autofilter do the example given in this help.
I did not know that filter existed :D
Thank you so much.
I tried recording the filtering action to later use in a macro, but when i run the macro, nothing from the file is copied and pasted. Do you know if the macro needs any special filtering functions?

Thanks a lot,
yet again! :)
Hi again,
here is my code which aims to use to filtering recorded from a normal excel workbook.
it runs with no errors, except it is not doing what i need.
I need it to open each csv. file in the chosen folder, select and copy the data within the specified ranges, and paste them into a new workbook. all of the data from the different filed found in the chosen folder should end up within a single workbook.
Are you able to point me in the right direction as to how to do this?



Sub Consolidate()
Dim fName As String, fPath As String, fPathDone As String
Dim LR As Long, NR As Long
Dim wbData As Workbook, wsLightningDataCompilation As Worksheet

'Setup
Application.ScreenUpdating = False 'speed up macro execution
Application.EnableEvents = False 'turn off other macros for now
Application.DisplayAlerts = False 'turn off system messages for now

'Set wsLightningDataCompilation = ThisWorkbook.Sheets("LightningDataCompilation") 'sheet report is built into

'Path and filename
'Prompting user to choose the required folder

MsgBox "Please select a folder with files to consolidate"
Do
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
If .SelectedItems.Count > 0 Then
fPath = .SelectedItems(1) & "\"
Exit Do
Else
If MsgBox("No folder chose, do you wish to abort?", _
vbYesNo) = vbYes Then Exit Sub
End If
End With
Loop

With wsLightningDataCompilation
If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
.Cells.Clear
NR = 1
Else
NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 'appends data to existing data
End If

fPathDone = fPath & "Imported\" 'remember final \ in this string
On Error Resume Next
MkDir fPathDone 'creates the completed folder if missing
On Error GoTo 0
fName = Dir(fPath & "*.csv*") 'listing of desired files


'Import a sheet from found files
Do While Len(fName) > 0
If fName <> ThisWorkbook.Name Then 'don't reopen this file accidentally
Set wbData = Workbooks.Open(fPath & fName) 'Open file

'This is the section to customize, replace with your own action code as needed

Columns("F:I").Select
Selection.ClearContents
Columns("A:E").Select
Selection.AutoFilter
Columns("C:C").Select 'Filter Latitude
ActiveSheet.Range("$A$1:$E$31001").AutoFilter Field:=3, Criteria1:= _
">=-37.9382", Operator:=xlAnd, Criteria2:="<=-32.004"
Columns("D:D").Select 'Filter Longitude
ActiveSheet.Range("$A$1:$E$31001").AutoFilter Field:=4, Criteria1:= _
">=136.7754", Operator:=xlAnd, Criteria2:="<=141.0698"
Cells.Select
Selection.Copy
Windows("GPATS arranged.xlsx").Activate
ActiveSheet.Paste

wbData.Close False 'close file
NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 'Next row
Name fPath & fName As fPathDone & fName 'move file to IMPORTED folder
fName = Dir 'ready next filename
End If
Loop
End With

ErrorExit: 'Cleanup
ActiveSheet.Columns.AutoFit
Application.DisplayAlerts = True 'turn system alerts back on
Application.EnableEvents = True 'turn other macros back on
Application.ScreenUpdating = True 'refreshes the screen
End Sub



Thank you kindly for your help,
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
did you "record" the macro or write it on your own. Both are good practices.
but when you edit the "recorded" macro you can tweak it. It is not necessary to make so many selections.
open a blank sheet and copy this data from A1 to D8

hdng1 hdng2 hdng3 hdng4
a 95 11 30
s 78 72 45
d 51 65 14
f 93 32 100
g 17 39 88
h 84 14 13
j 74 37 64


now first try this macro ( the filtered data is copied in sheet 2). if it is ok then study the code statements and you can get an idea.


Sub test()
Dim r As Range
Worksheets("sheet1").Activate
Set r = Range("A1").CurrentRegion
'MsgBox r.Address
r.AutoFilter field:=3, Criteria1:=">=25", Operator:=xlAnd, Criteria2:="<=70"
r.AutoFilter field:=4, Criteria1:=">=15", Operator:=xlAnd, Criteria2:="<=65"
r.SpecialCells(xlCellTypeVisible).Copy Worksheets("sheet2").Range("A1")
ActiveSheet.AutoFilterMode = False
End Sub


now you an use this to your file If there is still some doubts do not hesitate to post back.