Autofilter using reference table

[SammyCRX]

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
Sheets("Sheet1").Select
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
Cells.AutoFilter
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
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Name sheet
ActiveSheet.Name = shF.Range("A" & n)


'Clear filter
Sheets("Sheet1").Select
If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
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

Thanks,
Sam

 

[SammyCRX]

Any ideas please? It's a new day and I still can't figure it out

 

[Jerry Sullivan]

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

vArray=shF.Range("B1:E1").Value

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.