Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reversing order of text | Excel Discussion (Misc queries) | |||
Autofill- Reversing Order | Excel Discussion (Misc queries) | |||
reversing order of columns of data | Excel Programming | |||
Reversing the Order of a String | Excel Worksheet Functions | |||
Reversing the order of a chart | Excel Programming |