Reversing the order of a For-Each loop action?

[Henry]

To all you knowledgeable and helpful people,

I'm using a For-Each loop to run through a named Range and delete all
non-blank Rows. This only removes alternate Rows. I know it should be done
from the bottom of the Range upwards. Is it possible to get a For-Each loop
to start at the bottom of the Range and work upwards? I know how to do it in
a For-Next loop, but Step -1 obviously won't work if the loop counter is
pointing at the top Row to start with! The size of the Range, on entering
the loop, is indeterminate.

TIA
Henry

 

[Mike H]

Hi,

It is 'normal' to work backwards through a range to delete rows because
otherwise you can miss rows but it can be done going forward through the
range by building a new range to delete

Sub delete_Forward()
Dim MyRange As Range
Dim CopyRange As Range
Set MyRange = Range("A1:A10")
For Each c In MyRange
If WorksheetFunction.CountA(Rows(c.Row)) > 0 Then
If CopyRange Is Nothing Then
Set CopyRange = c.EntireRow
Else
Set CopyRange = Union(CopyRange, c.EntireRow)
End If
End If
Next
If Not CopyRange Is Nothing Then
CopyRange.Delete
End If
End Sub


Mike

 

[Jim Thomlinson]

There is no way to change the order of a for each. What you can do is to
create one big range to delete instead of deleting one row at a time. It is a
bit more code but it runs a fair bit faster...

dim rng as range
dim rngToDelete as range

for each rng in sheets("MySheet").range("MyRange")
if rng.value = "Something" then
if rngtodelete is nothing then
set rngtodelete = rng
else
set rngtodelete = union(rng, rngtodelete)
end if
end if
next rng

if not rngtodelete is nothing then rngtodelete.entirerow.delete

 

[Dave Peterson]

You may be able to build the range to delete and then use that.

Dim myRng as range
dim myCell as range
dim DelRng as range

with worksheets("sheet99")
set myrng = .range("a3:A99")
end with
for each mycell in myrng.cells
if mycell.value = "" then
if delrng is nothing then
set delrng = mycell
else
set delrng = union(delrng,mycell)
end if
end if
next mycell
if delrng is nothing then
'nothing to delete
else
delrng.entirerow.delete
end if

if you're really looking for blank rows, you could test it by using:

if application.counta(mycell.entirerow) = 0 then
instead of
if mycell.value = "" then

 

[Patrick Molloy]

here's an alternative approach...

Option Explicit
Sub deleteBlanks()
Dim source As Range
Dim cell As Range
Application.ScreenUpdating = False
Set source = Range("D41000")
Set cell = source.Find("")
Do While Not cell Is Nothing
Rows(cell.Row).Delete
Set cell = source.FindNext()
Loop
Application.ScreenUpdating = True
End Sub

 

[Mike H]

Hmm,

I think I had a bit of a brainstrom here!!

That means you want to keep blank rows so in effect you can delete
everything so why not

Dim MyRange As Range
Set MyRange = Range("A1:A10")
MyRange.EntireRow.Delete

Mike

 

[Henry]

Mike,

Won't deleting everything also delete the Range name?

Henry

 

[Henry]

Thanks Patrick,

That, suitably modified, will do the job.

Henry

 

[Patrick Molloy]

no worries. cheers & thanks for feedback

Thanks Patrick,

That, suitably modified, will do the job.

Henry