Delete row if cell contains a specified word

0
votes

Does anyone know a vba code to delete an entire row if any cells in column A contain the word "Total" ? For example, A38 contains "Total", delete that entire row. Next month, cell A44 contains the word "total", delete that entire row. And so on.... Thank you!

1
vba
So you want to 'loop' through all the 'Cells in row 1, column 1' and check the 'Cells.Value' to see if the word total is 'InStr' which would return something greater then 0. Then take the Cells.EntireRow.Delete it. Shouldnt be to hard if you give it a shot. – Sorceri
This link may be helpful. – Santosh
Another way using AUTOFILTER – Siddharth Rout

1 Answers

0
votes

Give the below a test run and see if it is close to what you are wanting.

Sub TestDeleteRows()
Dim rFind As Range
Dim rDelete As Range
Dim strSearch As String
Dim iLookAt As Long
Dim bMatchCase As Boolean

strSearch = "Total"

iLookAt = xlPart 'Change to xlWhole if the entire cell must equal search string
bMatchCase = False  'Change to True if you want search to be case sensitive

Set rDelete = Nothing

Application.ScreenUpdating = False

With Sheet1.Columns("A:A")
    Set rFind = .Find(strSearch, LookIn:=xlValues, LookAt:=iLookAt, SearchDirection:=xlPrevious, MatchCase:=bMatchCase)
    If Not rFind Is Nothing Then
        Do
            Set rDelete = rFind
            Set rFind = .FindPrevious(rFind)
            If rFind.Address = rDelete.Address Then Set rFind = Nothing
            rDelete.EntireRow.Delete
        Loop While Not rFind Is Nothing
    End If
End With
Application.ScreenUpdating = True
End Sub