![]() |
|
#5
|
||||
|
||||
|
The reason I did the second loop backward is easy to miss at first. Consider what happens if we do the loop the normal way, like this:
Code:
For jr = 1 to 6 If ows.Cells(jr, 7).Value = "Phase" then ows.Rows(jr).Delete Next jr 1) jr is set to 1 2) Checks G1; it's not "Phase", so it skips to the next record 3) jr is now 2 4) Checks G2; it's equal to "Phase", so it deletes row 2. 5) jr is now 3. 6) Checks G3... But wait! We just deleted row 2; so what was row 3 a moment ago is row 2 now, and row 4 is now row 3, and so on. So when I check row 3 (because jr is now 3), I skipped a row. G3—what was originally G3 but is now G2—has "Phase", but the above logic never looks at it. There's more than one way to get around that. My way is usually to run the loop backward, like this: Code:
For jr = 6 to 1 Step -1 If ows.Cells(jr, 7).Value = "Phase" then ows.Rows(jr).Delete Next jr Code:
For jr = 1 to 6
If ows.Cells(jr, 7).Value = "Phase" Then
ows.Rows(jr).Delete
jr = jr - 1
End If
Next jr
And the fact that I had to do the loop backward is why I had to have two loops in the first place. Until I noticed the above problem, I had a nice simple bit of logic with one loop, doing everything in one pass. Oh, well. |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
Can I pre-populate cells with a formula?
|
Box | Excel | 1 | 05-03-2014 11:55 PM |
| How to populate cells in Sheet2 with Data Source query using cell data from Sheet1 | bobznkazoo | Excel | 2 | 03-27-2014 11:14 AM |
How-TO format cells (FILL) by comparing cells
|
zanat0s | Excel | 1 | 07-03-2012 04:27 AM |
| Count range cells eliminating merge cells | danbenedek | Excel | 0 | 06-15-2010 12:40 AM |
| Populate cells in Word from a database | hotlilshan | Word | 3 | 12-09-2008 01:51 PM |