我试图在不手动添加名称的情况下过滤名称范围,而不是像现在这样只添加一个名称,从Pos_data
到T_Data
我的代码:
Dim category As Range
With Worksheets("Pos_Data")
Set category = .Range("U2")
End With
With Worksheets("T_Data")
With .Range("A1:CP1503")
.AutoFilter Field:=14, Criteria1:=category, VisibleDropDown:=True
End With
End With
End Sub
名称显示在Pos_Data
的U列中
问题是:如何有一个过滤器,从U列中选择所有名称,然后将其应用于T_Data column N
就像这里一样,我有名字,不需要手动在代码中写入它们,我想在新的选项卡列N 中过滤它们
您必须根据列U中的值构造一个名称数组。这个示例有望为您提供一个起点。
Option Explicit
Sub FilterForNames()
Dim categories As Range
Dim categoryList As Variant
'--- build an array of the names in the filter
With Worksheets("Pos_Data")
Set categories = .Range("U2:U5")
ReDim categoryList(1 To categories.Cells.Count)
Dim i As Long
For i = 1 To UBound(categoryList)
categoryList(i) = categories.Cells(i, 1).Value
Next i
End With
With Worksheets("T_Data")
With .Range("A1:CP1503")
.AutoFilter Field:=14, Operator:=xlFilterValues, Criteria1:=categoryList
End With
End With
End Sub