Cannot Delete Entire Row using For Loop

[aaronz07]

Hi Mr Excel Fans,

can you kindly help me with this code below: it will not delete the entire row when Column U contains data called "FREE", how ever it will change the colour of it


' Will not delete the entire row if data matched in column U

For i = 6 To LastRowFind

If Sheets("Sheet1").Cells(i, "U").Value <> "FREE" Then
Sheets("Sheet1").Cells(i, "U").EntireRow.Delete
End If
Next i


' same test code the colour entire row works
For i = 6 To LastRowFind

If Sheets("Sheet1").Cells(i, "U").Value <> "FREE" Then
Sheets("Sheet1").Cells(i, "U").EntireRow.Interior.Color = 12611584
End If
Next i

What am I doing wrong to delete the entire row?

Thanks

Aaron.

 

[jproffer]

One problem may be (or may eventually be, even if it doesn't seem to be now) that you're looping from the top down.

When deleting rows, you should really go from the bottom up, like this:

For i = LastRowFind To 6 Step -1
    
             If Sheets("Sheet1").Cells(i, "U").Value <> "FREE" Then
                     Sheets("Sheet1").Cells(i, "U").EntireRow.Delete
             End If
    Next i

You also say, it will not delete the row when U contains "FREE", but in your code you tell it to delete the row if U is NOT "FREE". Which one do you want to do?

 

[aaronz07]

yes,

if cells in column U contains the word "FREE" then delete the entire row please.

should it be this then:

Sheets("Sheet1").Cells(i).EntireRow.Delete
or
Sheets("Sheet1").Rows(i.EntireRow.Delete


both don't work, can you help?

 

[Norie]

Try this.

For i = LastRowFind To 6 Step -1

    If Sheets("Sheet1").Cells(i, "U").Value = "FREE" Then
        Sheets("Sheet1").Rows(i).Delete
    End If

Next i