CODEWITHSUNDEEP
excelvbafilter
DamnSpaceship
DamnSpaceship

Reputation: 55

VBA Filter Selected cells

I am trying to AutoFilter based on SelectedRange criteria. Ex. user selects cells with "shift" and/or "ctrl", clicks a button, which filters only the values that he had selected.

    Dim cel As Range
    Dim selectedRange As Range
    Dim arr As String

    Set selectedRange = Application.Selection

    arr = Empty
    For Each cel In selectedRange.Cells
        arr = arr & " " & cel.Value
    Next cel

    x = ActiveCell.Column
    
    lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row

    ActiveSheet.Range("$A$10:$DV" & lastrow).AutoFilter Field:=x, Criteria1:=arr, Operator:=xlFilterValues

I am tried populating a string and concatenating characters so that it looks like the line below, which works, but has fixed values:

Range("$A$1:$EZ" & lastrow).AutoFilter Field:=62, Criteria1:=Array("1", "2", "3", "4"), Operator:=xlFilterValues

I've also tried pasting selected values into a range, then transposing and calling the transposed range, however it still did not work.

Upvotes: 0

Views: 967

Answers (1)

Warcupine
Warcupine

Reputation: 4640

Use a delimited string to get the values from the selected range, then split that into an array to use as your criteria.

    Dim cel As Range
    Dim selectedRange As Range
    Dim splitstr As String
    Dim arr As Variant
    Dim lastrow As Long
    Dim x As Long
    
    Set selectedRange = Application.Selection

    For Each cel In selectedRange.Cells
        If splitstr = "" Then 'This avoids an empty index
            splitstr = cel.Value
        Else
            splitstr = splitstr & "|" & cel.Value
        End If
    Next cel
    
    x = ActiveCell.Column
    
    lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
    arr = Split(splitstr, "|")
    ActiveSheet.Range("$A$10:$DV" & lastrow).AutoFilter Field:=x, Criteria1:=arr, Operator:=xlFilterValues

Upvotes: 1

Related Questions