Macro to filter out everything but a list of words

[tonywatsonhelp]

Hi Everyone
I Sheet"Data" Range G6:G26" i have a list of words

In Sheet "Input" Range J10:J500 i have anothe list
I want a vba macro to filter "Input" Range J10:J500 to just show me the words in Sheet"Data" Range G6:G26"
please help if you can
Thanks
Tony

 

[duggie33]

In a workseet formula rather than VBA, you could try this...

=FILTER(Input!J10:J500,ISNUMBER(MATCH(Input!J10:J500,Data!G6:G26,0)))

Hope that helps,

Doug

 

[tonywatsonhelp]

thanks but i really need a macro

 

[tonywatsonhelp]

can anyone help please?

 

[mumps]

This code assumes you have a header in J9 of the Input sheet.

Sub FilterData()
    Application.ScreenUpdating = False
    Dim v As Variant, i As Long, arr() As Variant, x As Long
    v = Sheets("Data").Range("G6:G26").Value
    For i = LBound(v) To UBound(v)
        x = x + 1
        ReDim Preserve arr(1 To x)
        arr(x) = v(i, 1)
    Next i
    Sheets("Input").Range("J9").AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues
    Application.ScreenUpdating = True
End Sub

 

[tonywatsonhelp]

Thank you mumps

 

[mumps]

You are very welcome.

 

[tonywatsonhelp]

HI Mumps and everyone,
big problem, maybe i said this wrong but i want the filter to leave me everything but what in the list, your code hides everything but the list,
how can i get this to do it the otherway round?

 

[duggie33]

With a formula rather than VBA

=FILTER(Input!J10:J45,NOT(ISNUMBER(MATCH(Input!J10:J45,Data!G6:G124,0))))

Doug

 

[mumps]

Try:

Sub FilterData()
    Application.ScreenUpdating = False
    Dim v As Variant, v2 As Variant, i As Long, arr() As Variant, x As Long
    v = Sheets("Input").Range("J10:J500").Value
    v2 = Sheets("Data").Range("G6:G26").Value
    For i = LBound(v) To UBound(v)
        If IsError(Application.Match(v(i, 1), v2, 0)) Then
            x = x + 1
            ReDim Preserve arr(1 To x)
            arr(x) = v(i, 1)
        End If
    Next i
    Sheets("Input").Range("J9").AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues
    Application.ScreenUpdating = True
End Sub