Slow Structure - Row Deletion

[Kirk P.]

I've got this code to automate the selection and deletion of any rows that
contain a 0 (zero) in column AA. The code works fine, but rather slowly.
Takes almost 1 minute to delete the zero rows in 1 worksheet, and I want to
apply this code to ALOT of worksheets.

Is there anything that can be done to speed this up?

Dim FirstRow, LastRow, CurRow As Long
Dim col As String

FirstRow = 5
col = "AA"

LastRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
Application.ScreenUpdating = False
For CurRow = LastRow To FirstRow Step -1
If ActiveSheet.Cells(CurRow, col) = 0 Then Rows(CurRow).Delete
Next CurRow

MsgBox "Rows deleted!", vbInformation, "Status"

 

[Ron de Bruin]

See this page for a few ways to do it Kirk
http://www.rondebruin.nl/delete.htm

 

[Kirk P.]

Ron, we have a winner. The "auto filter" method is by far the fastest.

Thanks!

 

[Kirk P.]

I was hoping to apply this logic to ALL worksheets in a workbook via a loop.
I thought a For-Each statement would do it (code below), but I'm getting an
Object Required error when running the macro. What am I missing?

Sub Delete_with_Autofilter2()
Dim DeleteValue As String
Dim rng As Range
Dim w As Worksheet

DeleteValue = 0
' This will delete the rows with 0 (zero) in the Range("AA5:AA15000")

For Each w In Worksheets
With ActiveSheet
.Range("AA3:AA20000").AutoFilter Field:=1, Criteria1:=DeleteValue
With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With
Next w
End Sub

 

[Ron de Bruin]

Hi Kirk

This is wrong because you not change the sheet in the loop so you always point to the activesheet

Use this

Sub Delete_with_Autofilter2()
Dim DeleteValue As String
Dim rng As Range
Dim w As Worksheet

DeleteValue = 0

For Each w In Worksheets
With w
.Range("AA3:AA20000").AutoFilter Field:=1, Criteria1:=DeleteValue
With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Delete
End With
.AutoFilterMode = False
End With
Next w
End Sub

 

[Kirk P.]

That did it - thanks!

One final issue - it appears number formatting matters. There is one
worksheet that displays zeros in column AA as 0.00. All others are displayed
as 0. The code doesn't filter out and delete the 0.00, but it DOES work on
the 0.

How can I get it to work for BOTH 0.00 and 0 (i.e. zeros regardless of
formatting)?

 

[Ron de Bruin]

You can use the two criteria example on the site Kirk