EntireRow.ClearContents vs. EntireRow.Delete in a For Each loop

[bbott]

Can anyone tell me why this clears the contents of each row correctly:

    For Each c In Range("B9:B" & Range("B" & Rows.Count).End(xlUp).Row)
        If c.Value <> Range("A2") Then c.EntireRow.ClearContents
    Next c

But this does not delete the proper rows:

    For Each c In Range("B9:B" & Range("B" & Rows.Count).End(xlUp).Row)
        If c.Value <> Range("A2") Then c.EntireRow.Delete
    Next c

Is it because the Delete method is changing the row count? Any suggestions on how to fix?

 

[timfritsch]

You are on the right track - the .entirerow.delete does not work because deleting a row changes the row count.

You can try something like:

for loop = cells(rows.count,"B").end(xlup).row to 9 step -1
  if cells(loop,"B") <> Range("A2") then rows(loop).delete
next loop

Tim

 

[Blunder1]

try something like the below (untested)

for i = [COLOR=#333333]Range("B9:B" & Range("B" & Rows.Count).End(xlUp).Row) to 9 step -1
if cells(i,2).value <> cells(2,1).value then rows(i).delete
next i


[/COLOR]

 

[Gavin T]

...Is it because the Delete method is changing the row count?

That's exactly why.

Try this:

For i = Range("B" & Rows.Count).End(xlUp).Row To 9 Step -1
    If Range("B" & i).Value <> Range("A2") Then Rows(i).Delete
Next i

 

[Ferdi24]

Hi bbott you are correct, when row 4 is delted, the former row 5 is now row 4 and so on.
I usually use the specialcells trick to delete entire rows. This is also nice as you do not need a loop.

One way to do it:

Sub deleteExample()Dim c As Range
Dim data As Range


'this sets data to your cells in column B
Set data = Range("B9:B" & Range("B" & Rows.Count).End(xlUp).Row)
    
    'go one column to the right
    With data.Offset(0, 1)
     
     'set a formula that writes "X" if it matches cell A2
     .FormulaR1C1 = "=IF(RC[-1]=R2C1,""X"","""")"
     
     'Convert the formulas to actual values
     .Value = .Value
     
     'select all empty cells and delete the entire row in one go
     .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
End Sub

 

[bbott]

Thanks everyone. Using Step -1 worked well for me.