CODEWITHSUNDEEP
excelvbaautofilter
Matteo
Matteo

Reputation: 35

How to autofilter a column with values from a specific range?

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

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

The filter takes only the value of the last cell "C5" and not those of C2, C3, C4.

I tried to make it dynamic but is giving me an error every time.
Make it dynamic means that the range could be greater or smaller than C2:C5.

Upvotes: 1

Views: 485

Answers (2)

VBasic2008
VBasic2008

Reputation: 54815

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) = 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

Upvotes: 2

Sam
Sam

Reputation: 736

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"

Upvotes: 0

Related Questions