使用标题名称的自动筛选字段 [英] Autofilter Field using the header Name

问题描述

我尝试使用自动过滤器功能，但使用标题名称为"ID"的字段而不是列数.我已尽力而为，并收到以下错误消息:高级筛选器在迭代中失败，运行时错误1004"

i tried to use the Autofilter function but using field with the header name "ID" instead the number of column. i tried my best and i got this error message "Advanced filter fails at iterations, run-time error 1004"

我被困了两天.谢谢您的帮助

I am stuck for two days with it . thnk's for your help

代码:

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

推荐答案

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

The issue probably lies in not having anything selected for your .Autofilter to filter. Try replacing Selection with a range, or the .UsedRange.

您也不需要 WrkTab ，我看不到它有任何用途-在这里我使用 .Find :

You also don't need WrkTab, I don't see it having any purpose - here I use .Find instead:

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

I should add that it would be best for you to explicitly refer to your range instead of using UsedRange

这篇关于使用标题名称的自动筛选字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！