VBA Filter Selected cells-CodePudding

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.

CodePudding user response：

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