Autofilter using reference table

SammyCRX

New Member
Joined
Aug 15, 2016
Messages
34
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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Hi Sam,

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

Code:
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:

Code:
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top