admin管理员组文章数量:1334149
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
Share
Improve this question
edited Jan 13 at 15:45
CommunityBot
11 silver badge
asked Dec 4, 2024 at 11:08
joespeed1joespeed1
233 bronze badges
1
|
3 Answers
Reset to default 0With 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
版权声明:本文标题:excel - Filter then delete lines, on multiple sheets - Stack Overflow 内容由网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:http://www.betaflare.com/web/1742243608a2438991.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
lrow
to 1 and delete the row if col E is 0. Turnscreenupdating
off/on if needed. – CDP1802 Commented Dec 4, 2024 at 11:36