I am currently using the macro below to delete all hidden rows in the ActiveSheet due to an AutoFilter being applied. The problem is that it does not work on rows hidden due to an Advanced Filter. Any ideas how to modify the code to delete rows in the ActiveSheet hidden by both AutoFilter and Advanced Filter? Thanks.
Sub DeleteHiddenRows()
Dim rngHidden As Range
On Error Resume Next ' In case there's no hidden cells
Application.ScreenUpdating = False
With Range("A2:A" & Rows.Count)
Set rngHidden = .SpecialCells(xlCellTypeVisible)
If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData
'MsgBox rngHidden.Address
rngHidden.EntireRow.Hidden = True 'Hide previously visible cells
.SpecialCells(xlCellTypeVisible).EntireRow.Delete 'previously hidden cells
rngHidden.EntireRow.Hidden = False ' Unhide previously visible cells
End With
Application.ScreenUpdating = True
End Sub
Bookmarks