How to autofilter a column with values coming from a specific range-CodePudding

Hi all i hope you can help me with this issue since i just started learning VBA :)

I need to autofilter a column of an excel sheet with the values coming from a range in another sheet. Little complication, this range is dynamic and could include different numbers rows (each cell contains a different text) that the code should take and use to autofilter

Here is what i have written

Sub Filtrapp()
 
Worksheets("Applicazioni").Activate
Range("A8:C1000").AutoFilter 1, Worksheets("RecordTabella").Range("C2:C5").Value

End Sub

Issue here is that the filter takes only the value of the last cell "C5" and not those of C2,3,4 Also i tried to make it dynamic but is giving me error every time, hope someone can help

Thanks in advance

CodePudding user response：

Filter a Range Using An Array As Criteria

Sub Filtrapp()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim sws As Worksheet: Set sws = wb.Worksheets("RecordTabella")
    
    Dim srg As Range
    Dim srCount As Long
    
    With sws.Range("C2")
        Dim lCell As Range: Set lCell = .Resize(sws.Rows.Count - .Row   1) _
            .Find("*", , xlFormulas, , , xlPrevious)
        If lCell Is Nothing Then Exit Sub ' empty criteria column range
        srCount = lCell.Row - .Row   1
        Set srg = .Resize(srCount)
    End With
    
    Dim Data As Variant ' 2D one-based
    If srCount = 1 Then ' one cell (row)
        ReDim Data(1 To 1, 1 To 1): Data(1, 1).Value = srg.Value
    Else ' multiple cells (rows)
        Data = srg.Value
    End If
    
    Dim Arr() As String: ReDim Arr(1 To srCount) ' 1D one-based
    
    Dim r As Long
    
    For r = 1 To srCount
        Arr(r) = Data(r, 1)
    Next r
    
    Dim dws As Worksheet: Set dws = wb.Worksheets("Applicazioni")
    If dws.FilterMode Then dws.ShowAllData
    
    Dim drg As Range: Set drg = dws.Range("A8:C1000")
    
    drg.AutoFilter 1, Arr, xlFilterValues
    
End Sub

CodePudding user response：

Do you mean something like this?

This would filter by values that are >10 and <20

Worksheets("Applicazioni").Range("C1").AutoFilter Field:=3, Criteria1:=">10", _
    Operator:=xlAnd, Criteria2:="<20"