Deleting Rows with Specific Word along the Same Column VBA

[excelnoob1991]

Hi there,

It's my first time posting here; I apologise if I may not be following any guidelines.

I am trying to create a macro which helps delete any word in Column E.
For example, The word I would like to delete is "Cleaner" but in Column E, there are multiple selections such as BUILDING CLEANER, CLEANER L16- LAILA, FRANK CLEANER 2

I am interested to delete all the above. Any rows that have this specific word.
Tried searching for a solution, but I wasn't able to.
I have a header and my columns are from A to G.

Am aware a simple filter and delete would suffice, but I am trying to learn a bit more VBA knowledge so I can automate my repetitive work a little.

Thank you.

 

[kevin9999]

Please try this on a copy of your workbook. Run the macro with the sheet in question active.

Option Explicit     '<-- Always use this - forces declaration of variables/avoids problems
Option Compare Text '<-- Use this to avoid upper/lower case text comparison issues
Sub Delete_Cleaners()

    'Declare variables by type
    Dim LRow As Long, i As Long, a As Variant, b As Variant
    
    'Find last row with data in column E
    LRow = Cells(Rows.Count, "E").End(xlUp).Row
    
    'Load values into array a
    a = Range("E2:E" & LRow)
    
    'Resize array b to match array a
    ReDim b(1 To UBound(a), 1 To 1)
    
    'Loop through array a and if elements contain "CLEANER" then put a 1 into array b
    For i = 1 To UBound(a)
        If a(i, 1) Like "*CLEANER*" Then b(i, 1) = 1
    Next i
    
    'Output array b to the sheet from cell H2
    Range("H2").Resize(UBound(b, 1)).Value = b
    
    'Check if any values contained "CLEANER" and if so, sort by column H & delete those rows
    i = WorksheetFunction.Sum(Columns("H"))
    If i > 0 Then
        Range("A2:H" & LRow).Sort Key1:=Range("H2"), order1:=xlAscending, Header:=xlNo  '<-- sort
        Range("H2").Resize(i).EntireRow.Delete                                          '<-- delete
    End If

End Sub

 

[excelnoob1991]

It works!! THANK YOU!!!

It looks a tad bit confusing though hahah maybe cos I'm new.

I'm wondering if it's possible to add different elements.
I tried the below but not possible though.

For i = 1 To UBound(a)
        If a(i, 1) Like "*CLEANER*", "*TEMP*", "*VISITOR*" Then b(i, 1) = 1
    Next i

 

[kevin9999]

Happy to help & welcome to the Forum

One way to add elements is by using the OR operator, so you'd replace this:

For i = 1 To UBound(a)
        If a(i, 1) Like "*CLEANER*" Then b(i, 1) = 1
Next i

With this:

For i = 1 To UBound(a)
        If a(i, 1) Like "*CLEANER*" Or _
        a(i, 1) Like "*TEMP*" Or _
        a(i, 1) Like "*VISITOR*" Then b(i, 1) = 1
Next i

 

[excelnoob1991]

Happy to help & welcome to the Forum

One way to add elements is by using the OR operator, so you'd replace this:

For i = 1 To UBound(a)
        If a(i, 1) Like "*CLEANER*" Then b(i, 1) = 1
Next i

With this:

For i = 1 To UBound(a)
        If a(i, 1) Like "*CLEANER*" Or _
        a(i, 1) Like "*TEMP*" Or _
        a(i, 1) Like "*VISITOR*" Then b(i, 1) = 1
Next i

I could kiss you!!

THANK YOU SOOO MUCH!!!
Now to google other VBAs for Vlookups hahahaha

Thank you again Kevin!!