When I don't know the number of rows, I'll use something like this if there's a
column that can determine where the data stops:

Dim LastRow as long
....
with activesheet
'use column A to determine the last row
lastrow = .cells(.rows.count,"A").end(xlup).row
end with

Then I could use:

ActiveSheet.Range("A1:S" & lastrow).AutoFilter ....
(the dollar signs don't help in the code)

Or I just use the entire column:

activesheet.range("A:S").autofilter ...

=======
After the range is filtered, I'd use something like:

dim VisRng as range 'near the top
....
with activesheet
with .autofilter.range 'don't worry about the exact address
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then
'nothing visible
Set VisRng = Nothing
Else
'resize to avoid the header
'and come down one row
'single column of visible cells
Set VisRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End If
End With
End With

if visrng is nothing then
'do nothing
else
visrng.entirerow.delete
end if

======
Untested, uncompiled. Watch for typos!



melExcel2007 wrote:

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?

--

Dave Peterson