question

Norman-1492 avatar image
1 Vote"
Norman-1492 asked JErnestoAneiros-1330 commented

Excel VBA get Selected Filter Fields

I wanted to know how I can get the selected fields in a auto filter.
The idea here is that I want the fields that a user selects from a table, and I program in vba to apply that same filter to other sheet automatically.

Any help would be appreciated!

not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomClarkeLearn avatar image
1 Vote"
TomClarkeLearn answered JErnestoAneiros-1330 commented

Here you go, same answer but just formatted so future use.

 Function FilterCriteria(Rng As Range) As String
     Application.Volatile
     Dim Filter As String
     Filter = ""
        
     On Error GoTo Finish
        
     With Rng.Parent.AutoFilter
         If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
            
         With .Filters(Rng.Column - .Range.Column + 1)
            
             If Not .On Then GoTo Finish
                
             Filter = .Criteria1
                
             Select Case .Operator
                 Case xlAnd
                     Filter = Filter & " AND " & .Criteria2
                    
                 Case xlOr
                     Filter = Filter & " OR " & .Criteria2
                        
             End Select
            
         End With
            
     End With
        
 Finish:
 FilterCriteria = Filter
 End Function
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Tom,

Why do you use Application.Volatile in this case?

Thanks.

0 Votes 0 ·
Norman-1492 avatar image
2 Votes"
Norman-1492 answered Norman-1492 commented

Nvm I found it. Here it is for someone looking for similar thing:

Function FilterCriteria(Rng As Range) As String
Application.Volatile
Dim Filter As String
Filter = ""
On Error GoTo Finish
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then GoTo Finish
With .Filters(Rng.Column - .Range.Column + 1)
If Not .On Then GoTo Finish
Filter = .Criteria1
Select Case .Operator
Case xlAnd
Filter = Filter & " AND " & .Criteria2
Case xlOr
Filter = Filter & " OR " & .Criteria2
End Select
End With
End With
Finish:
FilterCriteria = Filter
End Function

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

sorry for messed up formatting

1 Vote 1 ·