Instead of using a For Each loop, use the SpecialCells property.

Dim rngAll as Range
Dim c as Range

set rngAll = Range("a1:a" & [a65536].End(xlUp).Row)
set c = rngAll.SpecialCells(xlCellTypeBlanks)

etc.

"davidm" wrote:


As a mental exercise, I wish to employ straight-down (as opposed to
back-up) loop to delete blank rows. Further, I wish to deploy a
construct where all cells that qualify (ie blank) are agglomorated and
deleted in one fell swoop at the end of the looping. As the code will
not be repeatedly deleting during passes in the loop, some precious
time will be gained to improve time efficiency. My effort thus far has
produced the following code which fails to "gather" the qualifying
cells.

Sub DeleteBlankrw()
Dim rng As Range
Dim c As Range
For Each c In Range("a1:a" & [a65536].End(xlUp).Row)
If IsEmpty(c) Then
Set rng = c
Set x = Union(rng, c)
End If
Next
MsgBox x.Address
x.Delete
End Sub

Any help will be appreciated.


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=393897