Delete all filtered rows from a macro?

[Joe M]

Fairly new and plugging my way through macros. On my current macro recording
task, I turn on filtering for all the columns in the second row (top row is a
title line). I set a specific filter on a specific column. I then highlight
and delete all the rows that come up after that filter gets turned on. Then
I turn the specific filter off and continue.

This records in the macro fine, but it wouldn't work week-to-week in real
life, because the rows to be deleted will change each week. When I go and
view the macro code, how can I modify it, after I turn the filter on, to
"delete all the rows shown after filtering"? (Of course I want to keep the
column titles and overall title row too). I basically want to issue some
sort of general command like that rather than highlighting and deleting a
specific range of rows to delete.

I know I can record highlighting the third row and scrolling way down past
where any data would normally be, and then delete, but that just seems like a
sloppy way to do it.

Any help/advice would be much appreciated. Thanks,

Joe

 

[Dave Peterson]

One way:

Option Explicit
Sub testme()
Dim wks As Worksheet
Dim VisRng As Range

Set wks = ActiveSheet

With wks
With .AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible).Count = 1 Then
MsgBox "only the headers are visible"
Else
'resize to avoid the header
'and come down one row
Set VisRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
VisRng.EntireRow.Delete
End If
End With
End With
End Sub