delete rows when column has specific word(s)

So you need to find all cells with the word "ABC" in column "B" and if its found then delete the whole row. Correct?

Thanks

Try this is a copy of your workbook. Data will be delete and It cannot be undone.

Sub DeleteRows()

Dim lrow As Long
Dim i As Long

lrow = Cells(Rows.Count, "B").End(xlUp).Row

Application.ScreenUpdating = False
    For i = lrow To 1 Step -1
        If Cells(i, "B").Value = "ABC" Then
            Cells(i, "B").EntireRow.Delete
        End If
    Next i
Application.ScreenUpdating = True

End Sub

Thanks

Thanks Fredlo2010

didn't work but also no error

Thanks again

Originally Posted by chrisblackman

didn't work but also no error

It works for me. Most likely its just a reference issue. Can you attach a small sample of your workbook?

Thanks

My apologies and will do in the future

Originally Posted by alansidman

Chris - Welcome to the forum, however........

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(I have added the code tags for you. Please comply with our rules in the future)

The code you have works too, but need few tweaking.

Sub Macro1()

  Dim FoundIt As Range
  Dim Rng As Range
  Dim SearchWord As String
  
    SearchWord = "ABC"
    Set Rng = Columns("H:H")  'This is column where the code searches
    
      Set FoundIt = Rng.Find(What:=SearchWord, _
                             After:=Rng.Cells(1, 1), _
                             LookIn:=xlValues, _
                             LookAt:=xlWhole, _
                             SearchOrder:=xlByRows, _
                             SearchDirection:=xlNext, _
                             MatchCase:=False)
                             
      If Not FoundIt Is Nothing Then
         'Set FoundIt = FoundIt.Resize(Rowsize:=5) ' if the aim is to delete entire row, no need for this line, but it may  not be 
         FoundIt.EntireRow.Delete
      End If
         
End Sub

Thanks for your help but I am unable to post attachment
I'm following instructions but don't see where to attach


I'm sorry

Did you try post # 8?

I did and nothing happened

Originally Posted by AB33

Did you try post # 8?

Try the attached.
The code was initially written for a loop code ,hence you see lots of commenting on some of the lines.

AB33 Thanks very much!!! it works

Originally Posted by AB33

Try the attached.
The code was initially written for a loop code ,hence you see lots of commenting on some of the lines.

Thanks a million

here is attachment
I am trying to delete rows where column H has certain word say "longmeadow"
or any word specific word that I need rows removed

hi, chrisblackman, option, please check attachment, run code "test" or press Run button

This one works and deletes

Sub Macro1()

  Dim FoundIt As Range
  Dim Rng As Range
  Dim SearchWord As String
  
    SearchWord = "longmeadow"
    Set Rng = Columns("H:H")  'This is column where the code searches
    
      Set FoundIt = Rng.Find(What:=SearchWord, _
                             After:=Rng.Cells(1, 1), _
                             LookIn:=xlValues, _
                             LookAt:=xlWhole, _
                             SearchOrder:=xlByRows, _
                             SearchDirection:=xlNext, _
                             MatchCase:=False)
                             
      If Not FoundIt Is Nothing Then
         'Set FoundIt = FoundIt.Resize(Rowsize:=5) ' if the aim is to delete entire row, no need for this line, but it may  not be
         FoundIt.EntireRow.Delete
      End If
         
End Sub

Here is a variant you can also try.

Sub DeleteRows()

Dim lrow As Long
Dim i As Long
Dim n As Long

'Enter all the values to delete below
Keywords = Array("longmeadow") '<-----Enter the words to look for here inside "" and separated by commas

lrow = Cells(Rows.Count, "A").End(xlUp).Row

Application.ScreenUpdating = False
For n = 0 To UBound(Keywords)
    For i = lrow To 1 Step -1
        If UCase(Cells(i, "H").Value) = UCase(Keywords(n)) Then
            Cells(i, "H").EntireRow.Delete
        End If
    Next i
Next n
Application.ScreenUpdating = True

End Sub

Thanks

Fredlo2010 Thanks so very much it works also

You are welcome