VBA Multiple Criteria / Variable Filter

Question

I need some help regarding filtering in excel, and would greatly appreciate any assistance. I would like to be able to filter by variable criteria, and possibly by multiple variable criteria. Allow me to explain this as simple as I possibly can

I have a workbook, in Sheet 2 I have data. In Sheet 1, I have a list of variables which I need to be filtered in sheet 2. These variables will vary in quantity, and of course the data in each cell will vary.

Now here is where I have my humble request(s).

Can I possibly have code for two separate functions:

1) For excel to register how many variables there are, and to filter each of these variables one by one (I have code that will do what I require and then reset the filter).

2) For excel to register the variables and filter all of them at the same time (multiple criteria).

I have attached a link to an example excel spreadsheet. I hope it helps!

http://www.filedropper.com/excelexample

I really appreciate any help on this.

Thank You

First iterate over the list, create a collection of dictionary of unique values, then iterate over that collection, applying each as a filter. — David Zemens
– David Zemens, Commented Oct 7, 2013 at 11:59

Gary's Student · Accepted Answer · 2013-10-07 13:06:56Z

2

In this small example, we gather the values from the Info tab, remove any blanks and apply that filter to the first column of the Data tab so all matches will be displayed:

Sub luxation()
    Dim sh1 As Worksheet, N As Long
    Dim st As String
    Set sh1 = Sheets("Info")
    N = sh1.Cells(Rows.Count, "A").End(xlUp).Row

    For i = 2 To N
        v = sh1.Cells(i, 1).Value
        If v <> "" Then
            st = st & v & ","
        End If
    Next i
    st = Mid(st, 1, Len(st) - 1)
    Arr1 = Split(st, ",")

    Sheets("Data").AutoFilterMode = False
    With Sheets("Data").Range("$A$1:$C$9244")
        .AutoFilter Field:=1, Criteria1:=Arr1, Operator:=xlFilterValues
    End With
End Sub

In the next example, the filter values are applied sequentially:

Sub luxation2()
    Dim sh1 As Worksheet, N As Long
    Dim st As String
    Set sh1 = Sheets("Info")
    N = sh1.Cells(Rows.Count, "A").End(xlUp).Row

    For i = 2 To N
        v = sh1.Cells(i, 1).Value
        If v <> "" Then
            st = st & v & ","
        End If
    Next i
    st = Mid(st, 1, Len(st) - 1)
    Arr1 = Split(st, ",")
    Sheets("Data").Activate
    For i = LBound(Arr1) To UBound(Arr1)
        Sheets("Data").AutoFilterMode = False
        With Sheets("Data").Range("$A$1:$C$9244")
            .AutoFilter Field:=1, Criteria1:=Arr1(i), Operator:=xlFilterValues
        End With
        MsgBox "Check out the filter"
    Next i
End Sub

answered Oct 7, 2013 at 13:06

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2298601 Over a year ago

Hi, I have another question. If possible your help would be appreciated. I created a macro for my colleague in France using the filter macros you helped me with (above), and it seems to error on the following line (error code 5) : st = Mid(st, 1, Len(st) - 1) They are using a French version of excel. It works fine for me, so I think it may be a language issue. Thank you for your help!

Gary's Student Over a year ago

First make sure MID and LEN are valid in French VBA. Then make sure st has something in it!!

user2298601 Over a year ago

Hi, I checked online and it would appear that MID and LEN are not valid in french, so I replaced them with the french functions (NBCAR and STXT), en.excel-translator.de/mid and en.excel-translator.de/len . However it now displays an error "Sub or function not defined" and highlights "NBCAR" in the line : st = STXT(st, 1, NBCAR(st) - 1) . There are values in st, so i'm not sure what to do next. Any help would be greatly appreciated. Thank You!

Collectives™ on Stack Overflow

VBA Multiple Criteria / Variable Filter

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related