Looping issue

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.

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
if rng is nothing then
Set rng = c
else
Set rng = Union(rng, c)
endif
End If
Next
if not rng is nothing then
MsgBox rng.Address
rng.entireRow.Delete
End if
End Sub


--
Regards,
Tom Ogilvy


"davidm" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
>
>

Many thanks Tom. I knew I was missing a bit of logic somewhere in the setting of my range object and which you've masterfully supplied. I can now proceed and translate the idea of "one-off" mass action (not necessarily for deletions) in some projects.


And thanks Eric for the input. I however cannot see how the use of Specialcells can demonstrate the use of overtly "pooling" like cells together. If my understanding of Specialcells method is correct, it does so but only implicitly or covertly. Thus,
Range("a:a").SpecialCells(xlCellTypeBlanks).EntireRow.delete will pick out all blank cells in one fell swoop and delete them. There is no place for direct "pooling"which was the object of my exercise.