find specific string within the worksheet and then delete all rows containing that text

[gint32]

Hi Everyone,

I am trying to find excel vba to find a specific string within the worksheet and then delete those rows (entirerow.delete) that contain that text anywhere within.

So far I cant see anything that I can maniplute to suit muyu purposes, I've found lots of vba that will search specific columns or vba that will search through the cells for exact text, but so far nothing that will search for example the words "*Cancelled*" or "*CANCELLED*" within a cell that contains other strings too such as "I Cancelled this order "...

Can anyone point me to some code that I may use to acheive this

 

[GWteB]

You could try this function ...

Public Function FindSomeText(ByVal argText As String, ByVal argSht As Worksheet) As Range
    Dim c As Range, StartAddr As String
    With argSht.UsedRange
        Set c = .Find(argText, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
        If Not c Is Nothing Then
            StartAddr = c.Address
            Do
                If Not FindSomeText Is Nothing Then
                    Set FindSomeText = Application.Union(FindSomeText, c)
                Else
                    Set FindSomeText = c
                End If
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> StartAddr
        End If
    End With
End Function

Usage example:

Public Sub gint32()

    Dim r As Range

    Set r = FindSomeText("canceled", ActiveSheet)
    r.EntireRow.Delete
End Sub

 

[Rick Rothstein]

Give this compact macro a try...

Sub RemoveRows()
  Cells.Replace "*Cancelled*", "#N/A", xlWhole, , False, , False, False
  On Error GoTo NoCancel
  Intersect(Cells.SpecialCells(xlConstants, xlErrors).EntireRow, Cells).Delete
NoCancel:
End Sub

 

[gint32]

You could try this function ...

Public Function FindSomeText(ByVal argText As String, ByVal argSht As Worksheet) As Range
    Dim c As Range, StartAddr As String
    With argSht.UsedRange
        Set c = .Find(argText, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
        If Not c Is Nothing Then
            StartAddr = c.Address
            Do
                If Not FindSomeText Is Nothing Then
                    Set FindSomeText = Application.Union(FindSomeText, c)
                Else
                    Set FindSomeText = c
                End If
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> StartAddr
        End If
    End With
End Function

Usage example:

Public Sub gint32()

    Dim r As Range

    Set r = FindSomeText("canceled", ActiveSheet)
    r.EntireRow.Delete
End Sub

look good and Thanks, if the word in the search matches it works but doesnt if it's not there ..error "object...variable not set", so canceled doesnt run and cancelled does run fine...thanks again

 

[Rick Rothstein]

look good and Thanks, if the word in the search matches it works but doesnt if it's not there ..error "object...variable not set", so canceled doesnt run and cancelled does run fine...thanks again

If you tried my code, it would have raised the same error... I modified my code in Message #3 to eliminate that problem.

 

[gint32]

This also looks good and alot smaller vba, but the only issue is if the word is not there its throws an error, I think I can sort that though ..thanks very much

 

[Rick Rothstein]

This also looks good and alot smaller vba, but the only issue is if the word is not there its throws an error, I think I can sort that though ..thanks very much

Refresh your browser and check out my code in Message #3 again... I added some additional code to trap that error so it should work fine for you now.

 

[GWteB]

it works but doesnt if it's not there ..error "object...variable not set", so canceled doesnt run and cancelled does run fine...thanks again

Yep, the usage example should have been like this:

Public Sub gint32()

    Dim r As Range

    Set r = FindSomeText("cancelled", ActiveSheet)
    If Not r Is Nothing Then
        r.EntireRow.Delete
    End If
End Sub

 

[gint32]

Yep, the usage example should have been like this:

Public Sub gint32()

    Dim r As Range

    Set r = FindSomeText("cancelled", ActiveSheet)
    If Not r Is Nothing Then
        r.EntireRow.Delete
    End If
End Sub

Thanks once again..your a champ