I have used Bullen's FilterCriteria VBA function in Excel 2003. But, now I am
using Excel 2007 and the function does not work. The result of the Intersect
function is always = Nothing.
Here is the function for reference:
Public Function FilterCriteria(ByRef Rng As Range) As String
Dim Filter As String
On Error GoTo Finish
Filter = ""
With Rng.Parent.AutoFilter
If Intersect(Rng, .Range) Is Nothing Then
GoTo Finish
End If
With .Filters(Rng.Column - .Range.Column + 2)
If Not .On Then
GoTo Finish
End If
Filter = .Criteria1
Filter = Right$(Filter, Len(Filter) - 1)
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