我正在开发一个Excel 2016 VBA宏,该宏会对标题列应用筛选器。之后,用户应用筛选条件。我希望能够在VBA中检索用户应用的筛选条件并将其保存到字符串数组中。是否有办法访问筛选条件?
Dim sht As Worksheet
Set sht = ActiveSheet
With sht.AutoFilter
With .Filters
ReDim filtarr(1 To .Count, 1 To 3)
For f = 1 To .Count
With .Item(f)
If .On Then
filtarr(f, 1) = .Criteria1
Debug.Print .Criteria1
If .Operator Then
filtarr(f, 2) = .Operator
filtarr(f, 3) = .Criteria2
Debug.Print .Operator & ", " & .Criteria2
End If
End If
End With
Next f
End With
End With
sht.AutoFilter.Range.Columns(f).Column
(即筛选范围的Columns(f)
列)确定的吗?(b) 如果用户选择了一个字段中的值列表而不仅仅是一个或两个值,则需要稍微调整代码(在这种情况下,Criteria1
将是一个变体数组,可以通过filtarr(f,1)(1)
、filtarr(f,1)(2)
等来访问)。 - YowE3Ksht.AutoFilter
是否不是Nothing
- 如果没有应用自动筛选,那么就无法访问其Filters
属性。 - Tim WilliamsPublic Function AutoFilterCriteria(ByVal WholeTable As Range) As String
On Error Resume Next
If WholeTable.Parent.AutoFilter Is Nothing Then ' if no filter is applied
AutoFilterCriteria = "None"
On Error GoTo 0
Exit Function
End If
Dim LongStr As String, FirstOne As Boolean
LongStr = ""
FirstOne = False
Dim iFilt As Integer
For iFilt = 1 To WholeTable.Parent.AutoFilter.Filters.Count ' loop through each column of the table
Dim ThisFilt As Filter
Set ThisFilt = WholeTable.Parent.AutoFilter.Filters(iFilt) ' look at each filter
On Error Resume Next
With ThisFilt
If .On Then
If FirstOne Then LongStr = LongStr & " AND " ' Get column title
LongStr = LongStr & "[" & WholeTable.Parent.Cells(WholeTable.Row - 1, WholeTable.Column + iFilt - 1).Value & ":"
On Error GoTo Handle
If .Operator = xlFilterValues Then ' dont really care to enumerate multiples, just show "multiple"
LongStr = LongStr & "<Multiple>]"
ElseIf .Operator = 0 Then
LongStr = LongStr & .Criteria1 & "]"
ElseIf .Operator = xlAnd Then
LongStr = LongStr & .Criteria1 & " AND " & .Criteria2 & "]"
ElseIf .Operator = xlOr Then
LongStr = LongStr & .Criteria1 & " OR " & .Criteria2 & "]"
End If
On Error GoTo 0
FirstOne = True
End If
End With
Next
AutoFilterCriteria = LongStr
On Error GoTo 0
Exit Function
Handle:
AutoFilterCriteria = "! Error !"
On Error GoTo 0
End Function
代码应该像这样。字段的代码是cells(1, f)。
Dim sht As Worksheet
Set sht = ActiveSheet
With sht.AutoFilter
With .Filters
ReDim filtarr(1 To .Count, 1 To 4) ' change array
For f = 1 To .Count
With .Item(f)
If .On Then
filtarr(f, 1) = .Criteria1
filtarr(f, 4) = Cells(1, f) 'field
Debug.Print .Criteria1, Cells(1, f)
If .Operator Then
filtarr(f, 2) = .Operator
filtarr(f, 3) = .Criteria2
Debug.Print .Operator & ", " & .Criteria2
End If
End If
End With
Next f
End With
End With