Excel VBA-从自动筛选器返回Criteria1数组 [英] excel VBA - return Criteria1 Array from an Autofilter
本文介绍了Excel VBA-从自动筛选器返回Criteria1数组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个表,我需要返回所有已切换的值.当我选择了我想要的宏之后录制宏时,看起来像这样
I have a table that I need to return all of the toggled values. When i record a macro after selecting which ones i want it looks like this
ActiveSheet.Range("$A$1:$P$1000").AutoFilter Field:=6, Criteria1:=Array("A" _
, "B", "C", "D", "E", "G"), Operator:=xlFilterValues
我遇到的问题是,将由用户过滤的a,b,c等值将始终在变化,因此我无法以这种方式对任何标准进行硬编码.
the problem i have is that the a,b,c, etc values that will be filtered by the user will always be changing so I can't hardcode any criteria that way.
有没有办法我可以返回一个类似于其外观的切换数组?
is there a way i can return an array of what is toggled on in a fashion similar to how this looks?
msgbox ActiveSheet.Range("$A$1:$P$1000").criteria1
推荐答案
以类似这样的数据开头:
Beginning with data like:
此子项应用过滤器:
Sub Macro1()
Range("A1:C22").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$C$22").AutoFilter Field:=3, Criteria1:=Array( _
"Alice", "Boris", "Mike"), Operator:=xlFilterValues
End Sub
此子项将:
- 确定要过滤的列
- 已进行选择的已过滤列中的
- 列出实际选择
Sub FilterInformation()
Dim st As String, ws As Worksheet, rg As Range, boo As Boolean
Set ws = ActiveSheet
On Error GoTo GetMeOut
Set rg = ws.AutoFilter.Range
MsgBox "Filter range" & vbCrLf & rg.Address
N = ws.AutoFilter.Filters.Count
MsgBox "Number of filters" & vbCrLf & N
For i = 1 To N
boo = ws.AutoFilter.Filters.Item(i).On
MsgBox i & "==>" & boo
If boo Then
MsgBox UBound(ws.AutoFilter.Filters.Item(i).Criteria1) & " items in array"
U = UBound(ws.AutoFilter.Filters.Item(i).Criteria1)
L = LBound(ws.AutoFilter.Filters.Item(i).Criteria1)
For j = L To U
MsgBox ws.AutoFilter.Filters.Item(i).Criteria1(j)
Next
End If
Next
Exit Sub
GetMeOut:
MsgBox ("no filters in sheet")
End Sub
这篇关于Excel VBA-从自动筛选器返回Criteria1数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文