Looping through and deleting rows w/o skipping

[mosta]

I know I can get around my problem with a different approach, like looping through row index numbers instead of for/each row, but I'm still curious if the problem can be averted more directly.

Namely: I reference a range. I use a For/Each loop to go through MyRange.Rows. If a row meets a condition, I delete that row. Problem is if the next row also meets the condition--it gets skipped by the 'Next' part of the loop. So in a block of rows to delete, I only get every other one.

Here is a code example with an attempted and failed solution:

Dim MyRange as Range
Dim Row as range

For Each Row in MyRange.Rows
     If Row.Cells(1, ColIndex) = "delete me" Then
          
          Set Row = Row.Offset(-1, 0)  'This could blow up on the first row...
          Row.Offset(1, 0).Delete

     End If
Next Row

The two lines inside the "delete me" condition were just the single line "Row.Delete" in my first attempt. That approach only got every other row. This second attempt did the same thing--skipping rows--however! I can't fool the next loop into not skipping the row after the deleted row it seems--vba remembers!

Am I missing something simple, to use For/Next for this task? Thanks for looking.

 

[tweedle]

You can go 'top to bottom' with Deletes, but it's not my usual practice for just this reason.
In doing so, you can create another range in which to to assign the qualified rows/cell-range. (Below I'm only using the Column A cell)

Then with the newly acquired range, one delete statement zings the whole batch without hosing the For Each loop's internal index.

Sub foo()
Dim MyRange As Range
Dim DelRange As Range   'New
Dim Row As Range
Set MyRange = Range("A10:A20")  'Testing
ColIndex = 1    'Testing
For Each Row In MyRange.Rows
     If Row.Cells(1, ColIndex) = "delete me" Then
          If DelRange Is Nothing Then
            Set DelRange = Cells(Row.Row, 1)
          Else
            Set DelRange = Union(DelRange, Cells(Row.Row, 1))
          End If
     End If
Next Row
DelRange.Rows.EntireRow.Select  'Testing
DelRange.Rows.EntireRow.Delete (xlUp)
Set DelRange = Nothing
'Shift Selection Focus from DelRange.Rows.EntireRow.Select
MyRange.Cells(1, 1).Select  'Testing
End Sub

 

[mosta]

Thanks for the suggestion. It makes sense and is probably cleaner than using row indexes. (I actually considered trying the Union function to delete all, as you've shown--and I got annoyed when Union failed on "Set rgUnion = Union(rgUnion, rgRow)" with the first row when rgUnion is Nothing. It looks like I'll come back around to that in the end...).

Thanks again.

 

[JackDanIce]

This may not be directly what you want, but something I use often is; instead of looping, I code a filter into my used range and then filter for what I want deleted, which avoids the problem of skipped rows.

I just stated a similar thing in another post, have a read of the code I suggested:

Sub DeleteRowsBasedonName ()

Application.DisplayAlerts = False

  If Sheets(1).Autofiltermode Then Sheets(i).Autofiltermode = False
  Sheets(1).Range("A1").AutoFilter Field:=1, Criteria1:="delete me"
  ActiveSheet.UsedRange.Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete

Application.DisplayAlerts = True
End Sub

If you want to keep the header row then change above to include:

  ActiveSheet.UsedRange.Offset(0,1).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete

In my opinion, depending on the number of rows you're looping through, this is quicker

 

[mosta]

Thanks, I like it. I've been due to learn more of these kinds of conditional searches or filters that Excel provides.

 

[JackDanIce]

No worries, glad you find it useful

 

[Jonmo1]

I find the best way is to make it go backwards from bottom to top...

Dim MyRange As Range, i As Long, FR As Long, LR As Long
FR = MyRange.Row
LR = FR + MyRange.Rows.Count - 1
 
For i = LR to FR Step - 1
    if Cells(i, ColIndex) = "delete me" Then Rows(i).EntireRow.Delete
Next i

Hope that helps.