Autofilter using reference table


New Member
Aug 15, 2016
Hi all,

Hopefully someone can put me out of my misery, as I've hit a brick wall with this one! So here's the plan:

Sheet1: Contains all the data, pasted in on a monthly basis
Sheet2 (Mapping Table): Contains the words which I'll be using in my autofilters

In future the names used in the report pasted into Sheet1 could change, so that's why I've tried to build a mapping table of sorts, rather than hard coding the exact words into the macro itself (I did write a macro like that, but I'm handing this over to someone to use in future and would like to 'future-proof' it).

So I've taken a few different approaches and nothing seems to work. The main problem I seem to get is that blank spaces in my mapping table seem to stop the autofilter from functioning correctly and I just end up with no information at all. I've allowed for up to five different options per column, but usually only 1-2 options per column are used. I know the code below is incorrect, as the autofilters will override the one above (so only the last one counts), but I tried this approach because I wanted to stop those blanks being an issue (clearly this doesn't work properly though). Hopefully it'll give an idea as to what I'm aiming for, which is to have up to five words used in the filter in four different columns.

'Sets how many companies we're looking forDim n As Long
For n = 1 To Application.InputBox(prompt:="Enter number of searches", Title:="Searches", Type:=1)

'Finds last row of data on the spreadsheet
LastRowWithValueInColumnA = Columns("A").Find("*", , xlValues, , xlRows, xlPrevious).Row

Dim wb As Workbook
Dim shF As Worksheet
Set wb = ActiveWorkbook
Set shF = wb.Sheets("Mapping Table")
If Sheets("Sheet1").FilterMode Then
End If
'Filtering Data
With wb.Sheets("Sheet1").Range("A1:BB" & LastRowWithValueInColumnA)
If shF.Range("B" & n).Value <> "" Then .AutoFilter Field:=20, Criteria1:=shF.Range("B" & n).Text
If shF.Range("C" & n).Value <> "" Then .AutoFilter Field:=20, Criteria1:=shF.Range("C" & n).Text
If shF.Range("D" & n).Value <> "" Then .AutoFilter Field:=20, Criteria1:=shF.Range("D" & n).Text
If shF.Range("E" & n).Value <> "" Then .AutoFilter Field:=20, Criteria1:=shF.Range("E" & n).Text
If shF.Range("F" & n).Value <> "" Then .AutoFilter Field:=20, Criteria1:=shF.Range("F" & n).Text
If shF.Range("G" & n).Value <> "" Then .AutoFilter Field:=24, Criteria1:=shF.Range("G" & n).Text
If shF.Range("H" & n).Value <> "" Then .AutoFilter Field:=24, Criteria1:=shF.Range("H" & n).Text
If shF.Range("I" & n).Value <> "" Then .AutoFilter Field:=24, Criteria1:=shF.Range("I" & n).Text
If shF.Range("J" & n).Value <> "" Then .AutoFilter Field:=24, Criteria1:=shF.Range("J" & n).Text
If shF.Range("K" & n).Value <> "" Then .AutoFilter Field:=24, Criteria1:=shF.Range("K" & n).Text
If shF.Range("L" & n).Value <> "" Then .AutoFilter Field:=2, Criteria1:=shF.Range("L" & n).Text
If shF.Range("M" & n).Value <> "" Then .AutoFilter Field:=2, Criteria1:=shF.Range("M" & n).Text
If shF.Range("N" & n).Value <> "" Then .AutoFilter Field:=2, Criteria1:=shF.Range("N" & n).Text
If shF.Range("O" & n).Value <> "" Then .AutoFilter Field:=2, Criteria1:=shF.Range("O" & n).Text
If shF.Range("P" & n).Value <> "" Then .AutoFilter Field:=2, Criteria1:=shF.Range("P" & n).Text
If shF.Range("Q" & n).Value <> "" Then .AutoFilter Field:=14, Criteria1:=shF.Range("Q" & n).Text
If shF.Range("R" & n).Value <> "" Then .AutoFilter Field:=14, Criteria1:=shF.Range("R" & n).Text
If shF.Range("S" & n).Value <> "" Then .AutoFilter Field:=14, Criteria1:=shF.Range("S" & n).Text
If shF.Range("T" & n).Value <> "" Then .AutoFilter Field:=14, Criteria1:=shF.Range("T" & n).Text
If shF.Range("U" & n).Value <> "" Then .AutoFilter Field:=14, Criteria1:=shF.Range("U" & n).Text
If shF.Range("V" & n).Value <> "" Then .AutoFilter Field:=1, Criteria1:=shF.Range("V" & n).Text
If shF.Range("W" & n).Value <> "" Then .AutoFilter Field:=1, Criteria1:=shF.Range("W" & n).Text
If shF.Range("X" & n).Value <> "" Then .AutoFilter Field:=1, Criteria1:=shF.Range("X" & n).Text
If shF.Range("Y" & n).Value <> "" Then .AutoFilter Field:=1, Criteria1:=shF.Range("Y" & n).Text
If shF.Range("Z" & n).Value <> "" Then .AutoFilter Field:=1, Criteria1:=shF.Range("Z" & n).Text

End With
'Copying Data post filtering
wb.Sheets("Sheet1").Range("A1:BB" & LastRowWithValueInColumnA).Offset(0, 0).SpecialCells(xlCellTypeVisible).Copy

Sheets.Add After:=ActiveSheet
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Name sheet
ActiveSheet.Name = shF.Range("A" & n)

'Clear filter
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
End If

Next n
Please could someone suggest where I'm going wrong or if there's a better way of approaching this?

I would be most grateful for any help or suggestions :)


Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Jerry Sullivan

MrExcel MVP
Mar 18, 2010
Hi Sam,

You will want to use this syntax for autofiltering the range for a list of values:

Range.AutoFilter Field:=20, Criteria1:=vArray, Operator:=xlFilterValues
where vArray is an array values from your Sheet2 mapping for each field.

You can read values into vArray like this:

Dim vArray as Variant


Blank cells are ignored if you have at least one non-blank value in the range read into vArray.
If all values in that range are blank, all rows in the be filtered out (hidden) - so you'll want to test that there's at least one non-blank before running a statement to filter a column field.
Last edited:

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics