Conditionally selecting a range of data
Closed
trini100
-
May 8, 2011 at 06:06 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - May 9, 2011 at 10:12 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - May 9, 2011 at 10:12 PM
Related:
- Conditionally selecting a range of data
- Tmobile data check - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- How to be offline in whatsapp when data is on - Guide
- How to take screenshot of selected area in excel - Guide
- Data transmission cable - Guide
6 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
May 8, 2011 at 10:09 PM
May 8, 2011 at 10:09 PM
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
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
Could you please explain it to a beginner?
Thank you so much
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
May 9, 2011 at 12:57 AM
May 9, 2011 at 12:57 AM
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.
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! :)
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! :)
Didn't find the answer you are looking for?
Ask a question
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,
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,
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
May 9, 2011 at 10:12 PM
May 9, 2011 at 10:12 PM
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.
now you an use this to your file If there is still some doubts do not hesitate to post back.
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.
