How to loop through excel autofilter where a criteria refers to a cell

Question

I need to loop through an autofilter where I have X number of columns and the filter in each column will be as per a criteria specified in another sheet

This is the working code and in this I have specified autofilters for 2 columns, in the actual data I have 50 columns and the criteria for field 2 depends on B2 and B3 in the worksheet named Shhet1, criteria for field 3 depends on C2 and C3 and so on uptil column 50

Sub Macro1()
   Sheets("Data").Select
   ActiveSheet.Range("Data").AutoFilter Field:=2, Criteria1:=Worksheets("Sheet1").Range("B2").Value & Worksheets("Sheet1").Range("B3").Value
   ActiveSheet.Range("Data").AutoFilter Field:=3, Criteria1:=Worksheets("Sheet1").Range("C2").Value & Worksheets("Sheet1").Range("C3").Value
End Sub

I have tried the following code but the use of cells.value is incorrect.

Sub Macro2()
Dim i As Integer
    Sheets("Data").Select
For i = 2 To 3
ActiveSheet.Range("Data").AutoFilter Field:=2, Criteria1:=Worksheets("Sheet1").Cells(i, 2).Value & Worksheets("Sheet1").Cells(i, 3).Value
Next i
End sub

JohnyL JohnyL · Accepted Answer · 2019-06-12T07:27:27

Sub FilterValues()

    Dim iColumn As Integer
    Dim rngData As Range
    Dim wksCriterias As Worksheet

    Set wksCriterias = Worksheets("Sheet1")
    Set rngData = Range("Data")

    With wksCriterias
        For iColumn = 2 To 3
            rngData.AutoFilter Field:=2, _
                               Criteria1:=Array(CStr(.Cells(2, iColumn)), CStr(.Cells(3, iColumn))), _
                               Operator:=xlFilterValues
        Next
    End With

End Sub

How to loop through excel autofilter where a criteria refers to a cell

1 Answers