I have a simplish problem that I hope has an equally simple answer - I've been using clumsy work-arounds to deal with it, but I KNOW there has to be a better way. Here it is:
I often use a loop to delete rows that meet certain criteria. For example:
The problem with this code arises if I have two consecutive "Blah" rows. If I delete row i, then the row below it suddenly becomes the new i-th row. However, since row i was already evaluated by the If statement, this new i-th row does not get evaluated, and the loop moves on to the Next i. Does this make sense? I feel like I might be explaining this weirdly.
One fix that I've tried only works when you know the maximum number of repeats you could have. Basically, if you'll never have more than 2 consecutive "Blah" rows , you can do the following:
This just does the loop twice, presumably cleaning up any remaining "Blah" rows that were missed the first time around. Ideally, I'd like something more flexible, that I can use when I have many repeated "Blahs" without having to spend 20 minutes looping.
Another solution I've used is to first sort so that the "Blahs" are at the bottom, then delete everything after the first "Blah" is found:
The problem with this one is that I'm using an empty cell to tell the loop to stop, but sometimes I want to use emptiness as my deletion criteria (in other words, empty cell = "Blah"), so that makes this annoying.
I feel like there's probably an awesome, mind-blowingly simple way to solve this, but I am stumped. Any suggestions?
Thank you SO MUCH for your help!!!
I often use a loop to delete rows that meet certain criteria. For example:
Code:
For i = 1 to x
If Cells(i, 3).Value = "Blah" Then
Rows(i).EntireRow.Delete
End if
Next i
The problem with this code arises if I have two consecutive "Blah" rows. If I delete row i, then the row below it suddenly becomes the new i-th row. However, since row i was already evaluated by the If statement, this new i-th row does not get evaluated, and the loop moves on to the Next i. Does this make sense? I feel like I might be explaining this weirdly.
One fix that I've tried only works when you know the maximum number of repeats you could have. Basically, if you'll never have more than 2 consecutive "Blah" rows , you can do the following:
Code:
For j = 1 to 2
For i = 1 to x
If Cells(i, 3).Value = "Blah" Then
Rows(i).EntireRow.Delete
End if
Next i
Next j
This just does the loop twice, presumably cleaning up any remaining "Blah" rows that were missed the first time around. Ideally, I'd like something more flexible, that I can use when I have many repeated "Blahs" without having to spend 20 minutes looping.
Another solution I've used is to first sort so that the "Blahs" are at the bottom, then delete everything after the first "Blah" is found:
Code:
For i = 1 To x
If Cells(i, 1).Value = "Blah" Then
Do Until Cells(i, 1).Value = ""
Rows(i).EntireRow.Delete
If .Cells(i, 1).Value = "" Then Exit Do
Loop
End If
Next i
The problem with this one is that I'm using an empty cell to tell the loop to stop, but sometimes I want to use emptiness as my deletion criteria (in other words, empty cell = "Blah"), so that makes this annoying.
I feel like there's probably an awesome, mind-blowingly simple way to solve this, but I am stumped. Any suggestions?
Thank you SO MUCH for your help!!!