根据在其他工作簿中找到的数据筛选工作簿列

Question

我有两个工作簿，我正试图根据在"Wbk1"的C列中找到的数据筛选"Wbk2"列A。

当您设置为单个单元格时，它工作得很好。我试图将整个列添加为筛选器，但似乎无法根据该条件对其进行筛选。

sub filter()
Dim Wbk1 As Workbook
Dim Wbk2 As Worksheet
Dim Wksht As Worksheet
Dim Filter As Range

Set Wbk1 = Workbooks(“DataToUseForFiltering.xlsm”)
Set Wksht = Wbk1.Worksheets(“Sheet1”)
Set Wbk2 = ActiveSheet
With Wksht
Set Filter = .Range(“C2”)
End With
With Wbk2
With .Range("A1:Z1" & Cells(.Rows.Count, "A").End(xlUp).Row)
.AutoFilter
.AutoFilter Field:=1, Criterial:=Filter
End With
End With
End Sub

Answer 1

代码建立在我之前的评论之上，并且经过了测试。

Option Explicit
Sub NewFilter()
Dim Wbk1 As Workbook
Dim Wksht1 As Worksheet
Dim Wksht2 As Worksheet
Dim rngFilter As Range
Dim aryFilter()
Dim i As Long
Set Wbk1 = Workbooks("DataToUseForFiltering.xlsm")
Set Wksht1 = Wbk1.Worksheets("Sheet1")
Set Wksht2 = ActiveSheet
With Wksht1
Set rngFilter = .Range("C2:C" & .UsedRange.Rows.Count)
End With
ReDim aryFilter(1 To rngFilter.Cells.Count)
For i = 1 To rngFilter.Cells.Count
aryFilter(i) = rngFilter.Cells(i).Value
Next i
Wksht2.Rows(1).AutoFilter _
Field:=1, _
Criteria1:=aryFilter, _
Operator:=xlFilterValues
End Sub