Is there a cleaner way to delete all visible cells except the headers?

Sub DeleteAllButTopNClear()
Application.ScreenUpdating = False
ActiveSheet.Range("A1:ZZ999999").Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

On Error Resume Next
Cells.Select
ActiveSheet.ShowAllData
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

I often need to filter out data that I don't need and have been using this macro for that. But I'm wondering if there isn't a better way that doesn't require selecting the range the way it does. I've been using that range just so it gets everything on the sheet.

Is there a cleaner way to delete all visible cells except the headers?

Sub DeleteAllButTopNClear()
Application.ScreenUpdating = False
ActiveSheet.Range("A1:ZZ999999").Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

On Error Resume Next
Cells.Select
ActiveSheet.ShowAllData
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

I often need to filter out data that I don't need and have been using this macro for that. But I'm wondering if there isn't a better way that doesn't require selecting the range the way it does. I've been using that range just so it gets everything on the sheet.

• excel
• vba
• filter

• You can avoid the .Offset(1, 0) by starting at row 2 instead of row 1. – BigBen Commented Mar 10 at 19:31

2 Answers 2

Please try to get the visible cells in Column A and then delete the corresponding rows.

Note: If the table (including the header row) doesn't start from row 1, you can't use UsedRange.Rows.Count to determine the last row number.

Microsoft documentation:

Worksheet.UsedRange property (Excel)

Range.EntireRow property (Excel)

Sub demo()
    Dim iRow As Long: iRow = ActiveSheet.UsedRange.Rows.Count
    Dim rCol As Range: Set rCol = Range("A2:A" & iRow)
    Dim rVis As Range
    On Error Resume Next
    Set rVis = rCol.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If Not rVis Is Nothing Then
        rVis.EntireRow.Delete
    End If
End Sub

btw, In most scripts, using Select is unnecessary.

How to avoid using Select in Excel VBA

Delete Auto-filtered Rows

• I usually combine these in the same sub.

Usage

Sub UsageExample()

    Dim wb As Workbook: Set wb = ThisWorkbook
    
    Dim ws As Worksheet: Set ws = wb.Sheets("Sheet1")
    ws.AutoFilterMode = False
    
    Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion
    
    rg.AutoFilter 1, "Completed"

    DeleteAutoFilteredRows ws

End Sub

The Method

Sub DeleteAutoFilteredRows(ByVal ws As Worksheet)

    ' Check for active filter.
    If Not ws.FilterMode Then
        MsgBox "There is no active filter on sheet """ _
            & ws.Name & """!", vbExclamation
        Exit Sub
    End If
    
    ' Check for active autofilter.
    If Not ws.AutoFilterMode Then
        MsgBox "There is no active autofilter on sheet """ _
            & ws.Name & """!", vbExclamation
        Exit Sub
    End If
       
    ' Reference the table range (has headers).
    Dim rg As Range: Set rg = ws.AutoFilter.Range

    ' Reference the data range (has no headers).
    Dim RowsCount As Long: RowsCount = rg.Rows.Count - 1
    If RowsCount = 0 Then
        MsgBox "There is no data in sheet """ _
            & ws.Name & """!", vbExclamation
        Exit Sub
    End If
    Dim drg As Range: Set drg = rg.Resize(RowsCount).Offset(1)
    
    ' Reference the filtered rows of the data range.
    Dim frg As Range:
    On Error Resume Next
        Set frg = drg.SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    ' Clear filters ('AutoFilter' stays turned on).
    ws.ShowAllData
    
    ' Delete the filtered rows of the data range.
    If Not frg Is Nothing Then frg.Delete Shift:=xlShiftUp

End Sub