rlv01
Well-known Member
- Joined
- May 16, 2017
- Messages
- 3,113
- Office Version
- 365
- 2010
- Platform
- Windows
I'm working on a routine to preserve the state of the user's autofilter picks for a given range of cells so they can later be restored. However, attempting to read filter criteria under certain circumstances produces runtime errors. Some questions:
1. For filters using only one criteria (criteria1), attempting to read criteria2 produces a runtime error. Is there a way to test for the existence of criteria2 without throwing an error, or am I going to be reduced to something like "On Error Resume Next"?
2. Setting a filter for background color produces a runtime error when trying to read the critera1 value for that filter. But if you set a font color filter, there are no issues reading the criteria1 value. Why?
1. For filters using only one criteria (criteria1), attempting to read criteria2 produces a runtime error. Is there a way to test for the existence of criteria2 without throwing an error, or am I going to be reduced to something like "On Error Resume Next"?
2. Setting a filter for background color produces a runtime error when trying to read the critera1 value for that filter. But if you set a font color filter, there are no issues reading the criteria1 value. Why?
VBA Code:
Sub AutoFilterTest()
Dim WS As Worksheet, I As Long, F As Filter
Set WS = ActiveSheet
'Clear old data
WS.AutoFilterMode = False
With WS.Cells(1, 1)
.EntireColumn.Clear
.Value = "Data"
End With
'Put some Data in Column1 to filter
For I = 2 To 100
If I < 100 Then
WS.Cells(I, 1).Value = "Red"
WS.Cells(I, 1).Interior.Color = RGB(255, 0, 0)
WS.Cells(I, 1).Font.Color = RGB(255, 255, 255)
End If
If I < 75 Then
WS.Cells(I, 1).Value = "Green"
WS.Cells(I, 1).Interior.Color = RGB(0, 255, 0)
WS.Cells(I, 1).Font.Color = RGB(0, 0, 0)
End If
If I < 50 Then
WS.Cells(I, 1).Value = "Yellow"
WS.Cells(I, 1).Interior.Color = RGB(255, 255, 0)
WS.Cells(I, 1).Font.Color = RGB(0, 0, 0)
End If
If I < 25 Then
WS.Cells(I, 1).Value = "Blue"
WS.Cells(I, 1).Interior.Color = RGB(0, 0, 255)
WS.Cells(I, 1).Font.Color = RGB(255, 255, 255)
End If
Next I
'Test getting existing filter properties
''''''''''''''''''''''
I = 1
'Set an autofilter
'Filter on cell contents
WS.UsedRange.AutoFilter Field:=1, Criteria1:="Green", Operator:=xlOr, Criteria2:="Blue"
'Inspect the autofilter properties
With WS.AutoFilter.Filters(1)
If .On Then
Debug.Print "State : Filter " & I & " is ON"
Debug.Print "Criteria1 : " & .Criteria1
Debug.Print "Criteria2 : " & .Criteria2 '<- no error
Debug.Print "Operator : " & .Operator
Else
Debug.Print "State : Filter " & I & " is OFF"
End If
End With
Debug.Print "---"
''''''''''''''''''''''
I = 2
'Set an autofilter
'Filter on cell contents
WS.UsedRange.AutoFilter Field:=1, Criteria1:="Yellow"
'Inspect the autofilter properties
With WS.AutoFilter.Filters(1)
'in place filter definitions
If .On Then
Debug.Print "State : Filter " & I & " is ON"
Debug.Print "Criteria1 : " & .Criteria1
Debug.Print "Criteria2 : " & .Criteria2 'this line will produce a runtime error (1004)
Debug.Print "Operator : " & .Operator
Else
Debug.Print "State : Filter " & I & " is OFF"
End If
End With
Debug.Print "---"
''''''''''''''''''''''
I = 3
'Set an autofilter
'Filter on cell color
WS.UsedRange.AutoFilter Field:=1, Criteria1:=RGB(0, 0, 255), Operator:=xlFilterCellColor
'Inspect the autofilter properties
With WS.AutoFilter.Filters(1)
'in place filter definitions
If .On Then
Debug.Print "State : Filter " & I & " is ON"
Debug.Print "Criteria1 : " & .Criteria1 'this line will produce a runtime error (438)
Debug.Print "Operator : " & .Operator
Else
Debug.Print "State : Filter " & I & " is OFF"
End If
End With
Debug.Print "---"
''''''''''''''''''''''
I = 4
'Set an autofilter
'Filter on font color
WS.UsedRange.AutoFilter Field:=1, Criteria1:=RGB(255, 255, 255), Operator:=xlFilterFontColor
'Inspect the autofilter properties
With WS.AutoFilter.Filters(1)
'in place filter definitions
If .On Then
Debug.Print "State : Filter " & I & " is ON"
Debug.Print "Criteria1 : " & .Criteria1 '<- no error
Debug.Print "Operator : " & .Operator
Else
Debug.Print "State : Filter " & I & " is OFF"
End If
End With
Debug.Print "---"
WS.ShowAllData
End Sub