Delete all Rows that do not contain string "Class Action" in Column F

Question

I have a spreadsheet with approximately 4000 rows and 15 columns. I want to see if see if the cells starting from F2 to F4000 contain the string "Class Action". If yes, I would like to keep the entire row; else, I would like to delete the rows that do not contain "Class Action".

I do not know whether it is important to note that the cells will contain long lists of job descriptions so even if a row has "Class Action" it may contain other jobs like "Corporate Law". I would still like to keep the rows so long as "Class Action" is in their F Column.

Thanks

score 1 · Accepted Answer · answered Jul 09 '14 at 18:28

Give this a try:

Sub RowKiller()
    Dim F As Range, rKill As Range
    Set F = Range("F2:F4000")
    Set rKill = Nothing
    For Each r In F
        v = r.Text
        If InStr(1, v, "Class Action") = 0 Then
            If rKill Is Nothing Then
                Set rKill = r
            Else
                Set rKill = Union(r, rKill)
            End If
        End If
    Next r

    If Not rKill Is Nothing Then
        rKill.EntireRow.Delete
    End If
End Sub

Thanks i'll try it out and let you know – user3821639 Jul 09 '14 at 18:37 — user3821639, Jul 09 '14 at 18:37

score 0 · Answer 2 · answered Jul 09 '14 at 18:25

0

Try this out.

Sub KeepClassA()

'get how many rows we have in column f
irows = Cells(Rows.Count, "F").End(xlUp).Row

'loop thru rows
For i = irows To 2 Step -1
'check if value does not contain Class ACtion
If Cells(i, 6).Value <> "Class Action" Then
'delete the row if true.
Cells(i, 6).EntireRow.Delete

End If
Next i


End Sub

answered Jul 09 '14 at 18:25

causita

1,607
1
20
32

Thanks I'll try it out and let you know – user3821639 Jul 09 '14 at 18:34

Delete all Rows that do not contain string "Class Action" in Column F

2 Answers2