Set autofilter criteria with VBA from a range

**mmogharreban** · 09-25-2015, 05:52 PM

I'm trying to set the criteria for an autofilter using VBA. I would like the values to come from a list on a separate sheet. So I have the autofilter on Sheet1 A:K and would like to set the criteria in column A to match all entries in column A of sheet 2, which will be a dynamic range. This is what I have pieced together and can't seem to get any further.

Sub test()

Dim rng As String
Dim lastrow As Long
Dim lastcell As Range
Dim myrange As Range

Set myrange = Sheet2.Range("A:A")

lastrow = Cells(100000, myrange.Column).End(xlUp).Row
lastcell = Cells(lastrow, 1)

For Each cell In Sheet2.Range("A2:" & lastcell).Cells.SpecialCells(xlCellTypeVisible)
rng = rng & Chr(34) & cell.Value & Chr(34) & ","


Next

Sheet1.Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("A:K").AutoFilter Field:=1, Criteria1:=Array( _
        rng), Operator:=xlFilterValues


End Sub

**stnkynts** · 09-25-2015, 06:45 PM

Try this:

Sub Filter_Range()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Sheet2")
Dim arr As Variant, arrVal() As String
Dim bDim As Boolean:    bDim = False
Dim i As Long

arr = ws2.Range("A1:A" & ws2.Range("A" & Rows.Count).End(xlUp).Row)

For i = LBound(arr, 1) To UBound(arr, 1)
    If bDim = False Then
        ReDim arrVal(0 To 0) As String
        arrVal(0) = arr(i, 1)
        bDim = True
    Else
        ReDim Preserve arrVal(0 To UBound(arrVal) + 1) As String
        arrVal(UBound(arrVal)) = arr(i, 1)
    End If
Next i

With ws1
    .AutoFilterMode = False
    .Range("A1:K" & .Range("A" & Rows.Count).End(xlUp).Row).AutoFilter 1, arrVal, xlFilterValues
End With
    
End Sub

**mmogharreban** · 09-28-2015, 10:06 AM

Works perfectly! Thank you!

Set autofilter criteria with VBA from a range

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Set autofilter criteria with VBA from a range

Re: Set autofilter criteria with VBA from a range

Re: Set autofilter criteria with VBA from a range

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] AutoFilter - Criteria - Array - Range - Not working please assist.

[SOLVED] Using an array from a range for Autofilter Criteria

[SOLVED] Autofilter range criteria filter only last cell value in range

Excel 2007 : Use Textbox.Value in AutoFilter Criteria (autofilter date)

Excel 2007 : Use Textbox.Value in AutoFilter Criteria (autofilter date)

Macro, autofilter, delete rows outside date range, defined names as criteria

AutoFilter method of Range class failed - Yet autofilter works.

Bookmarks

Bookmarks

Posting Permissions