excel - Filter then delete lines, on multiple sheets - Stack Overflow

admin管理员组

文章数量:1330917

I am trying to go through 135 tabs to filter each tab in Column E as zero, then delete any lines where the E cell equals 0.

My code returns the error.

Auto filter method of Range class failed.

It highlights the autofilter line.

If the first tab already has a filter it deletes the lines I want to delete but does not remove the filter. It gets stuck on this step.

How do make it work on every tab?

Sub DeleteEmptyRows()
    Dim ws As Worksheet
    Dim strSearch As String
    Dim lRow As Long

    For Each ws In ThisWorkbook.Sheets
        If (ws.Name <> "Data") And (ws.Name <> "Masterlist") And (ws.Name <> "Masterlist2") Then
            With ws
                lRow = .range("B" & .Rows.Count).End(xlUp).Row
                With .range("B1:B" & lRow)
                  .AutoFilter Field:=5, Criteria1:="0"
                  .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                End With
                ws.range("$B$1:$E$2500").AutoFilter Field:=1
            End With
        End If
    Next
End Sub

I am trying to go through 135 tabs to filter each tab in Column E as zero, then delete any lines where the E cell equals 0.

My code returns the error.

Auto filter method of Range class failed.

It highlights the autofilter line.

If the first tab already has a filter it deletes the lines I want to delete but does not remove the filter. It gets stuck on this step.

How do make it work on every tab?

Sub DeleteEmptyRows()
    Dim ws As Worksheet
    Dim strSearch As String
    Dim lRow As Long

    For Each ws In ThisWorkbook.Sheets
        If (ws.Name <> "Data") And (ws.Name <> "Masterlist") And (ws.Name <> "Masterlist2") Then
            With ws
                lRow = .range("B" & .Rows.Count).End(xlUp).Row
                With .range("B1:B" & lRow)
                  .AutoFilter Field:=5, Criteria1:="0"
                  .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                End With
                ws.range("$B$1:$E$2500").AutoFilter Field:=1
            End With
        End If
    Next
End Sub

• excel
• vba

Share Improve this question Follow edited Jan 13 at 15:45 CommunityBot 111 silver badge asked Dec 4, 2024 at 11:08 joespeed1joespeed1 2333 bronze badges 1

• Unless this task is time critical the simplest solution to scan the sheet upwards from lrow to 1 and delete the row if col E is 0. Turn screenupdating off/on if needed. – CDP1802 Commented Dec 4, 2024 at 11:36

Add a comment  | 

3 Answers 3

Sorted by: Reset to default 0

With the Range definition must include the row which is filtered (E).
Also this is in this case the 4th row of the Range.
To remove autofilter from the sheet use it without arguments.

With ws
            lRow = .Range("B" & .Rows.count).End(xlUp).Row
                With .Range("B1:E" & lRow)     'modified
                  .AutoFilter Field:=4, Criteria1:="0"   'modified
                  .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
                End With
                ws.Range("$B$1:$E$2500").AutoFilter  'modified
            End With
            

The Field parameter in Autofilter is the column that is being filtered. Because you are starting on column B in your original code, the field should have been 4 instead of 5. I recommend only filtering on the single column when possible to avoid this kind of confusion. Here is a small update to your code that will handle if there are any existing filters to avoid errors. I have commented it for clarity:

Sub DeleteEmptyRows()

    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim ws As Worksheet
    Dim lLastRow As Long
    
    For Each ws In wb.Worksheets
        Select Case LCase(ws.Name)  'convert sheet names to lowercase to avoid case matching errors
            Case "data", "masterlist", "masterlist2"   'compare against lowercase sheetnames, if these are the current worksheet then do nothing
            Case Else
                lLastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
                ws.AutoFilterMode = False   'Remove any existing filters anywhere on the worksheet
                With ws.Range("E1:E" & lLastRow)    'Filter directly on column E
                    .AutoFilter 1, 0                'There's only a single column, so the field must be 1, filter for value 0
                    .Offset(1).EntireRow.Delete     'No need to specify specialcells, the filter already ensures that so specialcells is redundant
                    .AutoFilter                     'Remove this filter
                End With
        End Select
    Next ws
    
End Sub

Delete Filtered Rows

• If you want to delete blank rows, use "" instead of "0".

Sub DeleteZeroRows()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Application.ScreenUpdating = False
    
    Dim ws As Worksheet, rg As Range, drg As Range, vrg As Range
    Dim IsSheetFound As Boolean

    For Each ws In wb.Worksheets
        IsSheetFound = False
        Select Case ws.Name
            Case "Data", "Masterlist", "Masterlist2"
            Case Else: IsSheetFound = True
        End Select
        If IsSheetFound Then
            ws.AutoFilterMode = False ' turn off autofiltering
            Set rg = ws.Range("A1").CurrentRegion ' includes headers
            Set drg = rg.Resize(rg.Rows.Count - 1).Offset(1) ' no headers
            rg.AutoFilter Field:=5, Criteria1:="0" ' for blanks use ""
            Set vrg = Nothing ' reset
            On Error Resume Next
                Set vrg = drg.SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            ws.AutoFilterMode = False ' turn off autofiltering
            If Not vrg Is Nothing Then vrg.Delete Shift:=xlShiftUp
        End If
    Next ws
    
    Application.ScreenUpdating = True
    
    MsgBox "Zero-rows deleted.", vbInformation

End Sub

本文标签： excelFilter then delete lineson multiple sheetsStack Overflow