Delete multiple rows of data based on criteria in a single cell

**slaga9** · 03-11-2011, 03:58 PM

I need to filter through the name and address of a client list- deleting entires that have a DO NOT CALL listed in C coloum.

I cant attach the file as I have client information in it, but in short I have 3 coloums, with multiple rows

each entry has a name, a phone number, an address, a city, and if they are "OK" or "Do Not Call"

Is set up as such

A -----------------------------B-----------------------------------------C

Name
#------------------------------#---------------------------------"Do Not Call"
Address
City

Name
#------------------------------#---------------------------------"OK"
Address
City

Name
#------------------------------#---------------------------------"Do Not Call"
Address
City

What I need to do is delete the entry if they have "Do not call" in C

meaning, i need to delet the entire row directly above it, the row that has (do not call) and the 2 below it.

Is this possible?

Thanks

**HSV** · 03-11-2011, 04:24 PM

Try this code once a testfile.

Sub tst()
   Dim i As Integer, cl As Variant
    For i = Cells(Rows.Count, 3).End(xlUp).Row To 1 Step -1
      For Each cl In Range(Cells(2, 3), Cells(Cells(Rows.Count, 3).End(xlUp).Row, 3))
    If cl = "do not call" Then
        cl.Offset(-1).Resize(4).EntireRow.Delete
     End If
    Next cl
  Next i
End Sub