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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

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,899
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,899
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,303
Messages
5,836,520
Members
430,437
Latest member
Emilycr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top