How to combine 2 macros deleting rows with certain criteria

[cwills]

Hi

I currently have 2 macros set up to search a sheet for different words and delete the row if that word is present.

I would like to combine these into a single macro which would check for both words and delete the row if either of them are present

The words are in different columns
I search for the word "resident" in column G with this

' Delete Any resident stores
Dim ws As Worksheet
Dim strSearch As String
Dim lRow As Long

strSearch = "Resident"

Set ws = Sheets("Site data")
With ws
lRow = .Range("G" & .Rows.Count).End(xlUp).Row
'~~> Remove any filters
.AutoFilterMode = False
'~~> Filter, offset(to exclude headers) and delete visible rows
With .Range("G1:G" & lRow)
.AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

'~~> Remove any filters
.AutoFilterMode = False
End With

And I then search for the word "Limited" in Column H using this

Dim ws As Worksheet
Dim strSearch As String
Dim lRow As Long

strSearch = "Limited"
Set ws = Sheets("Site data")
With ws
lRow = .Range("H" & .Rows.Count).End(xlUp).Row
'~~> Remove any filters
.AutoFilterMode = False
'~~> Filter, offset(to exclude headers) and delete visible rows
With .Range("H1:H" & lRow)
.AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

'~~> Remove any filters
.AutoFilterMode = False
End With

What's the best way to combine these 2 into a single macro to search for both?

Thank you
Colin

 

[NateSC]

I would probably add the code as a separate Sub that you can call with given parameters. That way you can add another call if needed or steal it and take it to another macro later.

You can create an advanced filter to filter 2 columns at the same time, but you have to have the criteria in a range (unless I am mistaken). The macro could write this range itself and then delete it or the filter criteria could already be on a sheet somewhere, but to me it is easier to just call the RemoveRows sub twice like below.

Sub ProcessRows()

    Dim ws As Worksheet
     
    Set ws = Sheets("Site data")

    Call RemoveRows(ws, "Resident", "G")
    Call RemoveRows(ws, "Limited", "H")

End Sub

Sub RemoveRows(ws As Worksheet, strSearch As String, myCol As String)

    Dim lRow As Long
 
    With ws
        lRow = .Range(myCol & .Rows.Count).End(xlUp).Row
        '~~> Remove any filters
        .AutoFilterMode = False
        '~~> Filter, offset(to exclude headers) and delete visible rows
        With .Range(myCol & "1:" & myCol & lRow)
            .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
            .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With
        
        '~~> Remove any filters
        .AutoFilterMode = False
    End With

End Sub