Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Filter a column for a list of names with VBA

Copper Contributor

Hi everyone,

 

I am trying to filter a column for a list of names on another sheet with VBA. The column that I am filtering in is not part of a table or pivot table and I am unable to change that, it is just a regular column with a filter at the top.

 

Below is the VBA I have so far, which does not give any errors when I run it, but instead of filtering the column for the names in the list, it filters out everything. Not sure where I went wrong and I appreciate the help as always. Thank you!

  

Sub FilterClosers()

    Dim Criteria As Variant
    Criteria = Worksheets("Deal Admin List").Range("F2:F19")
    Worksheets("Input").Range("AT2:AT100").AutoFilter Field:=46, Criteria1:=Criteria, Operator:=xlFilterValues
    Sheets("Deal Admin List").Visible = False

End Sub

 

3 Replies
Worksheets("Input").Range("AT2:AT100").AutoFilter Field:=1, Criteria1:=Criteria, Operator:=xlFilterValues
best response confirmed by BriceChapman (Copper Contributor)
Solution

@BriceChapman Your "Criteria" array needs to be horizontal (in a single row). Try using Application.Transpose on the criteria range as follows:

 

    Criteria = Application.Transpose(Worksheets("Deal Admin List").Range("F2:F19").Value)
This was it, thank you again djclements!
1 best response

Accepted Solutions
best response confirmed by BriceChapman (Copper Contributor)
Solution

@BriceChapman Your "Criteria" array needs to be horizontal (in a single row). Try using Application.Transpose on the criteria range as follows:

 

    Criteria = Application.Transpose(Worksheets("Deal Admin List").Range("F2:F19").Value)

View solution in original post