Range Next

[FGM]

Hi,
Windows 2000, Excel 2002
Is there a way to move to the next range in the middle of this procedure?
would like to loop through the range a given number at a time. Have several
different ways I want to use it in deleting rows in a given range.
Would like to be able to loop through range and delete a given number of
rows and keep a given numbers of rows as I go. But the following does not
work.

thanks in advance.
For Each rng In rng
rng.Select
For i = 1 To intGroup
ActiveCell.Offset(1, 0).Select

Next i

For i = 1 To myRowCount
Selection.EntireRow.Delete
Next i


Next

 

[ilia]

I don't really understand the specifics of your problem, but here are
some observations.

If your For Each loop uses the same rng for Each and In parts, you're
going to run into problems because you lose the original range
object. You could have something like this:

Public Sub loopRange()
Dim rng1 as Excel.Range
Dim rng2 as Excel.Range

Set rng2 = Application.ActiveSheet.Range("A1:Z1000")

For Each rng1 in rng2
' do stuff with each cell
Next rng1

' the result will be $A$1:$Z$1000
Debug.Print rng2.Address

End Sub


Here's the difference:

Public Sub loopRange2()
Dim rng1 as Excel.Range
Dim rng2 as Excel.Range

Set rng2 = Application.ActiveSheet.Range("A1:Z1000")

For Each rng1 in rng2
' do stuff with each cell
Next rng1

' the result will be $Z$1000
' you can't loop through the range again
Debug.Print rng2.Address

End Sub



You're doing an awful lot of selecting and ActiveCell and stuff in
your loops. You don't need to actually select a cell to process it.
So, rng.EntireRow.Delete works better.

If you're running into the problem where you have a row deleted and
your counter skips, just reduce it by one, like this:

For i = 1 to myRowCount
rng.EntireRow.Delete
i = i - 1
Next i


Hope that helps.

 

[FGM]

I don't really understand the specifics of your problem, but here are
some observations.

If your For Each loop uses the same rng for Each and In parts, you're
going to run into problems because you lose the original range
object. You could have something like this:

Public Sub loopRange()
Dim rng1 as Excel.Range
Dim rng2 as Excel.Range

Set rng2 = Application.ActiveSheet.Range("A1:Z1000")

For Each rng1 in rng2
' do stuff with each cell
Next rng1

' the result will be $A$1:$Z$1000
Debug.Print rng2.Address

End Sub


Here's the difference:

Public Sub loopRange2()
Dim rng1 as Excel.Range
Dim rng2 as Excel.Range

Set rng2 = Application.ActiveSheet.Range("A1:Z1000")

For Each rng1 in rng2
' do stuff with each cell
Next rng1

' the result will be $Z$1000
' you can't loop through the range again
Debug.Print rng2.Address

End Sub



You're doing an awful lot of selecting and ActiveCell and stuff in
your loops. You don't need to actually select a cell to process it.
So, rng.EntireRow.Delete works better.

If you're running into the problem where you have a row deleted and
your counter skips, just reduce it by one, like this:

For i = 1 to myRowCount
rng.EntireRow.Delete
i = i - 1
Next i


Hope that helps.

Thank you.... this will be something I will look at. I did solve my
problem by counting the number of rows in the range and then putting in a
counter so I went through that number of rows... counting the deletes etc...
I think that reading what you wrote will educate me .... always appreciate
the help.... thanks much.