时间戳

首页 > 解决方案 > VBA过滤选定的单元格

excel - VBA过滤选定的单元格

问题描述

我正在尝试根据 SelectedRange 标准进行自动筛选。前任。用户使用“shift”和/或“ctrl”选择单元格，单击一个按钮，该按钮仅过滤他选择的值。

    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

我尝试填充一个字符串并连接字符，使其看起来像下面的行，它有效，但具有固定值：

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

我也尝试将选定的值粘贴到一个范围中，然后转置并调用转置的范围，但它仍然不起作用。

标签： excelvbafilter

解决方案

使用分隔字符串从选定范围中获取值，然后将其拆分为一个数组以用作您的条件。

    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

推荐阅读