Grab all filtered rows for some action

[Max]

In an autofiltered range, I currently select all the filtered rows for
deletion manually. Is there a way to automate this manual selection of
filtered rows bit once I select any cell within the filtered rows? Thanks

 

[Ken Johnson]

In an autofiltered range, I currently select all the filtered rows for
deletion manually. Is there a way to automate this manual selection of
filtered rows bit once I select any cell within the filtered rows? Thanks

After selecting the whole lot go Edit|Goto|Special|Visible cells only|
OK

Ken Johnson

 

[Max]

After selecting the whole lot go Edit|Goto|Special|Visible cells only|

OK

When I tried the above (eg: recorded macro below), it unfortunately also
grabs the header row in the selection. The header row should not be
selected, only the filtered rows. How can the header row be deselected?

Sub Macro1()
Range("B3:F17").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="33"
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.EntireRow.Delete
End Sub

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim VRng As Range

Set VRng = Nothing
On Error Resume Next
With ActiveSheet.AutoFilter.Range
'avoid the header row???
Set VRng = .Resize(.Rows.Count - 1, 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End With
On Error GoTo 0

If VRng Is Nothing Then
MsgBox "no visible details"
Else
'do something with the visible rows
msgbox vrng.address(0,0)
End If

End Sub

 

[Max]

That works fine. Thanks, Dave.