Thread: problem deleting entire row based on a condition

Hey everyone

I have a problem deleting rows based on a condition, i didn't know how to use offset method.

i'm using a table like this one :

A B C
1 Main design1 FFR0
2 Extra design2 FFR0
3 Main design3 FFR0
4 Extra design4 FFR0
.
.
n Main designn FFR0


I want to delete entire row if "FFR0" in column C exists in a row beginning by "EXTRA" (column A)

thanks in advance for your help
regards

Hi,

Code:

Sub Macro1()
Dim rcell As Range
For Each rcell In Range("A:A")
If Left(rcell.Value, 5) = "Extra" And Left(rcell.Offset(0, 2).Value, 4) = "FFR0" Then rcell.EntireRow.Delete
Next rcell
End Sub

There's no need to evaluate the entire column A, and if you don't start at the bottom and work UP, you'll end up skipping rows. My suggestion:

Code:

Sub DeleteRows()
Dim i As Long, LastRow As Long
LastRow = Range("A" & Rows.Count).End(xlUp).Row

    For i = LastRow To 1 Step -1
        If LCase(Cells(i, "A").Value) Like "extra*" And _
            UCase(Cells(i, "C").Value) Like "FFR0*" Then _
                Rows(i).EntireRow.Delete
    Next i
End Sub