Filter an Excel Column by a Name List Using VBA AutoFilter
Question details
Filter a worksheet column using a list of names stored in a range on another worksheet through VBA.
- Product
- Microsoft Excel VBA AutoFilter
- Device & OS
- not provided
- Scenario
- The user has a vertical list of names on the "Deal Admin List" worksheet and wants to show only matching names in column AT on the "Input" worksheet.
- Observed behavior
- AutoFilter requires the multiple criteria values to be supplied as a horizontal array when filtering by xlFilterValues.
Confirm that cell AT2 is the header for the data range and that the names to filter are in Deal Admin List!F2:F19. Ensure both the source names and the Input column use matching text values without unintended spaces.
Transpose the name list and apply AutoFilter with VBA
Use this method when the criteria names are stored vertically on another worksheet and you need to filter a single Excel column for any matching name.
AutoFilter with Operator:=xlFilterValues accepts multiple filter values as an array. Transpose the vertical source range into a horizontal array, then use Field:=1 because AT is the first and only column in the filter range.
In Excel, press Alt+F11, then select Insert > Module to create or open a standard VBA module.
Add this line to read the name list and transpose it into a horizontal array: Criteria = Application.Transpose(Worksheets("Deal Admin List").Range("F2:F19").Value).
Use the following statement to filter the one-column range: Worksheets("Input").Range("AT2:AT100").AutoFilter Field:=1, Criteria1:=Criteria, Operator:=xlFilterValues.
Place the statements inside a Sub procedure, then press F5 in the VBA editor or choose Developer > Macros in Excel and select the macro to run it.
Filter Name Lists with VBA in WPS Spreadsheet
WPS Spreadsheet provides a familiar worksheet interface for managing name lists, filtering data, and working with compatible spreadsheet files. You can organize the criteria range, review filtered records, and use macro-enabled workbooks in one lightweight office suite.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheets, and open the workbook containing the Input and Deal Admin List worksheets.
- 2. Check the criteria names: Open the Deal Admin List sheet and verify that the names are listed in cells F2:F19.
- 3. Review the filter column: Open the Input sheet and confirm that AT2 contains the column header and the names to filter are below it.
- 4. Use the macro-enabled workbook: Save or open the file as an .xlsm workbook when your workflow requires the VBA macro, then run the available macro from the workbook's macro tools.
Frequently Asked Questions
Why does VBA need Application.Transpose for the filter criteria?
The range Deal Admin List!F2:F19 returns a vertical two-dimensional array. Application.Transpose converts it to a horizontal array that AutoFilter can use with Criteria1 and Operator:=xlFilterValues.
Why should I use Field:=1 instead of Field:=46 for column AT?
Field refers to the column number inside the range passed to AutoFilter, not the worksheet column number. For Range("AT2:AT100"), AT is the first and only column, so use Field:=1.
Does the AutoFilter range need a header row?
Yes. In Range("AT2:AT100"), cell AT2 is treated as the header and rows below it are filtered. If your header is in a different row, adjust the range to start at that header cell.
How can I clear the existing filter before running the macro again?
Before applying the new filter, use Worksheets("Input").ShowAllData when a filter is active. Check that Worksheets("Input").FilterMode is True first to avoid an error when no rows are filtered.
