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

On May 8, 7:59 am, "Max" wrote:
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

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

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 wrote:

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

--

Dave Peterson

That works fine. Thanks, Dave.