Results 1 to 3 of 3

Thread: Autofilter using reference table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2016
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Autofilter using reference table

    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


  2. #2
    New Member
    Join Date
    Aug 2016
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Re: Autofilter using reference table

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

  3. #3
    MrExcel MVP
    Jerry Sullivan's Avatar
    Join Date
    Mar 2010
    San Diego, California
    Post Thanks / Like
    11 Post(s)
    1 Thread(s)

    Default Re: Autofilter using reference table

    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 by Jerry Sullivan; Mar 8th, 2019 at 12:58 AM.
    Using Excel 2016

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts