Looping through and deleting rows w/o skipping

mosta

Board Regular
Joined
Jun 5, 2008
Messages
56
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:

Code:
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.
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
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.

Code:
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

Board Regular
Joined
Jun 5, 2008
Messages
56
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,685
Office Version
  1. 365
Platform
  1. Windows
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:
Rich (BB code):
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:
Rich (BB code):
  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

Board Regular
Joined
Jun 5, 2008
Messages
56

ADVERTISEMENT

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

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,685
Office Version
  1. 365
Platform
  1. Windows
No worries, glad you find it useful
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
I find the best way is to make it go backwards from bottom to top...

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,331
Messages
5,528,052
Members
409,800
Latest member
camronmartin

This Week's Hot Topics

Top