M
melExcel2007
I want a macro to work for a worksheet name which will always be the same
directory and file name but the data inside worksheet will periodically
change - increase in rows/decrease in rows. # of Columns/Column names will
not change. I'm trying to filter for a non-changing set of info, then delete
the results.
Here's what I get if I record my current run:
Workbooks.Open Filename:= _
"***Directory of location and file name here***"
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$S$746").AutoFilter Field:=5,
Criteria1:=Array("10" _
, "11", "12", "13"), Operator:=xlFilterValues
Rows("2:28").Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$S$719").AutoFilter Field:=5
Rows("1:1").Select
Selection.AutoFilter
I see the ActiveSheet.Ranges will need to change with each incarnation of
the worksheet. So, I could probably set these to A1:S60000 as the
spreadsheet will never get beyond that many rows. But, selecting the results
of filter - rows to delete portion is problematic. Any suggestions? Or
should I filter and set the Operator to delete? If so, what would that look
like?
directory and file name but the data inside worksheet will periodically
change - increase in rows/decrease in rows. # of Columns/Column names will
not change. I'm trying to filter for a non-changing set of info, then delete
the results.
Here's what I get if I record my current run:
Workbooks.Open Filename:= _
"***Directory of location and file name here***"
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$S$746").AutoFilter Field:=5,
Criteria1:=Array("10" _
, "11", "12", "13"), Operator:=xlFilterValues
Rows("2:28").Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$S$719").AutoFilter Field:=5
Rows("1:1").Select
Selection.AutoFilter
I see the ActiveSheet.Ranges will need to change with each incarnation of
the worksheet. So, I could probably set these to A1:S60000 as the
spreadsheet will never get beyond that many rows. But, selecting the results
of filter - rows to delete portion is problematic. Any suggestions? Or
should I filter and set the Operator to delete? If so, what would that look
like?