CODEWITHSUNDEEP
excelcontainsdelete-rowvba
Jacob Heglar
Jacob Heglar

Reputation: 11

VBA on how to delete rows that do not contain text

I have a column (H) that I need to search if it contains 1 of 4 words. If it does not contain 1 of those 4 words I need the row to either be hidden or deleted. What is the best way to go about acheiving this?

Upvotes: 0

Views: 4279

Answers (2)

Abe Gold
Abe Gold

Reputation: 2347

Or if you prefer to delete:

lastRow = Range("H65000").End(xlUp).Row
For i = lastRow To 2 Step -1
Select Case Cells(i, 8).Value
    Case "Red", "Blue", "Green", "White"
        'Do nothing
    Case Else
        Cells(i, 8).EntireRow.Delete
End Select
Next i

Upvotes: 0

Andy F
Andy F

Reputation: 47

Try adding the following to a module and running. I have assumed column H has a header so the range begins on row 2.

Public Sub Test()
Dim rng As Range
Dim row As Range
Dim cell As Range

Set rng = Range("H2:H7")

For Each row In rng.Rows
For Each cell In row.Cells

Select Case cell.Value
Case "Red", "Blue", "Green", "White"
'Do nothing
Case Else
row.Hidden = True
End Select

Next cell
Next row
End Sub

Upvotes: 2

Related Questions