Reversing the order of a For-Each loop action?
 

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

Reversing the order of a For-Each loop action?
 

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
"Henry" wrote:

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

Reversing the order of a For-Each loop action?
 

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

Jim Thomlinson


"Henry" wrote:

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

Reversing the order of a For-Each loop action?
 

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




Henry wrote:

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

--

Dave Peterson

Reversing the order of a For-Each loop action?
 

here's an alternative approach...

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

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

Reversing the order of a For-Each loop action?
 

Hmm,

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

I'm using a For-Each loop to run through a named Range and delete all
non-blank Rows.

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

"Mike H" wrote:

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
"Henry" wrote:

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

Reversing the order of a For-Each loop action?
 

Mike,

Won't deleting everything also delete the Range name?

Henry

Mike H wrote:
Hmm,

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

I'm using a For-Each loop to run through a named Range and delete
all non-blank Rows.

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

"Mike H" wrote:

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
"Henry" wrote:

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

Reversing the order of a For-Each loop action?
 

Thanks Patrick,

That, suitably modified, will do the job.

Henry

Patrick Molloy wrote:
here's an alternative approach...

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

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

Reversing the order of a For-Each loop action?
 

no worries. cheers & thanks for feedback

"Henry" wrote in message
...
Thanks Patrick,

That, suitably modified, will do the job.

Henry

Patrick Molloy wrote:
here's an alternative approach...

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

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