CODEWITHSUNDEEP
vbaexcel
user2672288
user2672288

Reputation:

excel vba - autofilter from selected range

I'm trying to create an autofilter where the criteria is an array made up of cells selected on the worksheet.

I was thinking of something like

dim crit as variant
set crit = selection ' i know this isnt right

ActiveSheet.Range("$A$2:$AC$476").AutoFilter Field:=1, Criteria1:=Array(crit), Operator:=xlFilterValues

I'm not sure how to build up the array of selected cells to pass as a criteria though. Any tips appreciated.

Upvotes: 2

Views: 1664

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149335

Is this what you are trying?

Sub Sample()
    Dim crit As Range
    Dim Ar() As Variant

    '~~> Check if what the user selected is a valid range
    If TypeName(Selection) <> "Range" Then
        MsgBox "Select a range first."
        Exit Sub
    End If

    Set crit = Selection
    Ar = crit.Value

    ActiveSheet.Range("$A$1:$C$7").AutoFilter Field:=1, _
                                              Criteria1:=Application.Transpose(Ar), _
                                              Operator:=xlFilterValues
End Sub

Upvotes: 1

Rory
Rory

Reputation: 34075

You need something like this:

Dim crit()                As Variant
Dim n                     As Long
Dim rgCell                As Range

ReDim crit(1 To Selection.Count)

n = 1
For Each rgCell In Selection.Cells
    crit(n) = CStr(rgCell.Value)
    n = n + 1
Next rgCell

ActiveSheet.Range("$A$2:$AC$476").AutoFilter Field:=1, Criteria1:=crit, Operator:=xlFilterValues

Upvotes: 1

Related Questions