vba 使用 VBScript 自动筛选 Excel 列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13950959/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel column autofiltering using VBScript
提问by arun_roy
Suppose I have a matrix like below :
假设我有一个如下矩阵：
EMPID Type Of employment
#1 10 "T"
#2 11 "P"
#3 20 "P"
#4 40 "T"
#5 50 "T"
#6 15 "P"
#7 19 "T"
After Filtered
过滤后
EMPID Type Of employment
#2 11 "P"
#3 20 "P"
#6 15 "P"
Can you people suggest me How to do it in vbscript? I know the syntax of VBA,but in VBScipt no idea how to format?
你能建议我如何在 vbscript 中做到这一点吗？我知道 VBA 的语法，但在 VBScipt 中不知道如何格式化？
And after filtering If I use CountA on column#1 should I get the Row numbers as 3or 7?Too much confused I am here.
过滤后如果我在第 1 列上使用 CountA，我应该将行号设为3还是7？我在这里太困惑了。
UPDATE
更新
objSheet2.Range("B1").AutoFilter 2, "Parent",,,False
to remove auto-filtering i used the code -objSheet2.Rows("1:1").Select.AutoFilterbut getting an error Unable to get the select property of the Range Class
要删除自动过滤，我使用了代码 -objSheet2.Rows("1:1").Select.AutoFilter但出现错误Unable to get the select property of the Range Class
CorrectedobjSheet2.Range("B1").AutoFilter 2, "Parent",,,TrueobjSheet2.AutoFilterMode = False
更正objSheet2.Range("B1").AutoFilter 2, "Parent",,,TrueobjSheet2.AutoFilterMode = False
Query:once the rows are filtered,then how would I recognize the actuall row numers of the filtered data rows,when looping over them?
查询：一旦行被过滤，那么在循环遍历它们时，我将如何识别过滤数据行的实际行号？
ANSWER
回答
For rowIndex=2 To objExcel1.Application.WorksheetFunction.CountA(ob1.Columns(1))
If objSheet2.Rows(rowIndex).Hidden Then
' do nothing - row is filtered out
Else
MsgBox(rowIndex)
End If
Next
This way I got the Filtered rows actual row number. Please advice if any better idea you guys post:-)
这样我就得到了过滤行的实际行号。如果你们有更好的想法，请提出建议:-)
Screen shot
截屏
Thanks
谢谢
回答by Scott Holtzman
I think this will get you want you are after a little faster.
我认为这会让你想要更快一点。
objSheet2.Range("B1").AutoFilter 2, "Parent",,,True
Dim rngFilter as Range
Set rngFilter = objXL.Application.Intersect(objSheet2.UsedRange,objSheet2.UsedRange.Offset(1),objSheet2.Columns(1)).SpecialCells(xlCellTypeVisible)
msgbox "Filtered range has " & rngFilter.Rows.Count & " rows."
'do with the range as you wish.
Dim cel as objXL.Range 'or just leave this off
For each cel in rngFilter
msgbox cel.row
Next
objSheet2.AutoFilterMode = False
In this way you are only selecting the visible cells after being filtered. You can then load that range into an array or loop through it as you see fit.
通过这种方式，您只能选择过滤后的可见单元格。然后，您可以将该范围加载到一个数组中，或者根据需要循环遍历它。
