Looping issue
 

As a mental exercise, I wish to employ straight-down (as opposed t
back-up) loop to delete blank rows. Further, I wish to deploy
construct where all cells that qualify (ie blank) are agglomorated an
deleted in one fell swoop at the end of the looping. As the code wil
not be repeatedly deleting during passes in the loop, some preciou
time will be gained to improve time efficiency. My effort thus far ha
produced the following code which fails to "gather" the qualifyin
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

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=39389

Looping issue
 

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" wrote in
message ...

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

Looping issue
 

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

Looping issue
 

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).Entir eRow.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.


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