It would be interesting to see how the two methods compare against each
other (with Calculations and ScreenUpdating both on and off). Back in the
"hide the rows" thread, the OP there ran my old delete line-by-line code
against the Union technique I am proposing here (both with just
ScreenUpdating off) and the time for 65536 rows involved went from 8+
minutes to 4 seconds, so this Union technique seems pretty snappy.

Rick


"broro183" wrote in message
...
hi all,

I think that using an Autofilter approach with manual calculation (filter
columns as required, select visible rows, change to manual calc', delete
rows
& return to automatic calculation) will be much faster than individually
checking each row. This can be tested on a copy of your data by recording
a
macro of these actions & then timing it. It can be more flexible if
needed.

Another change which may increase the efficiency of the code that's been
provided is to temporarily change the calculation mode to manual using:
Application.Calculation = xlCalculationManual
& then returning it to automatic at the end using
Application.Calculation = xlCalculationAutomatic


hth
Rob

__________________
Rob Brockett
NZ
Always learning & the best way to learn is to experience...



"Rick Rothstein (MVP - VB)" wrote:

Based on a comment about the speed of the routine I posted back in the
"hide
rows with 0 in Column G" thread which I used to develop the original code
for my answer in this thread, I "believe" the code below, incorporated
into
the structural change you added to my original code for OP's question,
will
be much more efficient (especially if the number of data rows is large)
since it delays interacting with the worksheets until all the rows to be
deleted are grouped into a range of their own...

Sub DeleteRowIfZeroInA()
Dim R As Range
Dim RowsToDelete As Range
Dim W As Worksheet
Dim LastRow As Long
For Each W In Worksheets(Array("Sheet3", "Sheet4"))
With W
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For Each R In .Range("A1:A" & CStr(LastRow))
If R.Value = 0 And R.Value < "" Then
If RowsToDelete Is Nothing Then
Set RowsToDelete = R
Else
Set RowsToDelete = Union(R, RowsToDelete)
End If
End If
Next
RowsToDelete.EntireRow.Delete xlShiftUp
Set RowsToDelete = Nothing
End With
Next
End Sub

Rick


"Gary''s Student" wrote in
message
...
Use a loop:

Sub DeleteRowIfZeroInA()
Dim X As Long
Dim R As Range
Dim LastRow As Long
For Each w In Worksheets(Array("Sheet3", "Sheet4"))
With w
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
For X = LastRow To 1 Step -1
If .Cells(X, "A").Value = 0 And .Cells(X, "A").Value < "" Then
.Cells(X, "A").EntireRow.Delete xlShiftUp
End If
Next
End With
Next
End Sub
--
Gary''s Student - gsnu200773