excel - 使用标题名称的自动过滤字段

Question

我尝试使用自动过滤功能，但使用标题名称为“ID”的字段而不是列数。我尽力而为，我收到此错误消息“高级过滤器在迭代时失败，运行时错误 1004”

我被它困了两天。谢谢你的帮助

代码 :

Sub AdataPreparation()
Dim WorkBk As Workbook, WorkSh As Worksheet, WrkTab As range, FilterRow As Variant
Set WorkBk = Workbooks.Open(Filename:="C:\Users\Documents\DataApplied.xlsm")
Set WorkSh = Sheets("sheet2") 
WorkSh.Activate 
Set WrkTab = range("A1").CurrentRegion 
WrkTab = ActiveRange
FilterRow = Application.Match("ID", WrkTab, 0)
Selection.AutoFilter Field:=FilterRow, Criteria1:="="
End Sub

Answer 1

问题可能在于没有为您的.Autofilter 选择任何内容。过滤。尝试替换 Selection有一个范围，或 .UsedRange .

你也不需要WrkTab ，我认为它没有任何目的 - 这里我使用 .Find反而:

Sub AdataPreparation()
Dim WorkBk As Workbook, WorkSh As Worksheet, FilterRow As Variant

Set WorkBk = Workbooks.Open(Filename:="C:\Users\Documents\DataApplied.xlsm")
Set WorkSh = Sheets("sheet2")

WorkSh.Activate

FilterRow = Rows("1:1").Find(What:="ID", LookAt:=xlWhole).Column

WorkSh.UsedRange.AutoFilter Field:=FilterRow, Criteria1:="="
End Sub

我应该补充一点，您最好明确引用您的范围，而不是使用 UsedRange