CODEWITHSUNDEEP
vbavariablesexcelfilter
user2298601
user2298601

Reputation: 45

VBA Multiple Criteria / Variable Filter

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

Upvotes: 2

Views: 16850

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 2

Related Questions