Excel VBA to filter multiple columns but only 1 criteria

Question

So I have 4 columns and thousands of rows. I want to look for one word that appears either in column 3 OR 4 and get rid of the rest. So filtering doesn't appear to work as I apply the filter in one column and this hides any row where the word doesn't exist, but it may exist in the next column.

I have tried:

lr = Cells(Rows.Count, 1).End(xlUp).Row
With ActiveSheet.Range("$A$1:$D$" & lr)

AutoFilter Field:=3, Criterial:="Test1" or AutoFilter Field:=4, Criterial:="Test1"

End With

So I suspect it will need something more clever.

Answer 1

Filter Data Using AdvancedFilter

Formula in Sheet2!E2

=OR(Sheet1!C2="Test1",Sheet1!D2="Test1")

Before

After

VBA

Sub FilterData()

    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Source Range
    Dim sws As Worksheet: Set sws = wb.Sheets("Sheet1")
    Dim srg As Range: Set srg = sws.Range("A1").CurrentRegion
    
    ' Criteria Range
    Dim cws As Worksheet: Set cws = wb.Sheets("Sheet2")
    Dim crg As Range: Set crg = cws.Range("A1").CurrentRegion
    
    ' Filter.
    srg.AdvancedFilter xlFilterInPlace, crg

End Sub