Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Next
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range Next
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. On Nov 9, 11:58 am, FGM wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
Range.Find returns cell outside of range when range set to single cell | Excel Programming | |||
how to? set my range= my UDF argument (range vs. value in range) [advanced?] | Excel Programming |