MrExcel Publishing
Your One Stop for Excel Tips & Solutions

AutoFilter range Value

Posted by David Vespoli on February 10, 2002 1:45 PM

Anyone know how I can set a variable to the current range in an Autofilter sort?


Posted by Pisanio on February 10, 2002 2:46 PM

Set rng = Worksheets("Sheet1").Range("A1").CurrentRegion.SpecialCells(xlVisible)

Posted by Posthumus on February 10, 2002 3:14 PM

If, instead, you are looking for the Autofilter criteria setting, the following function is from Stephen Bullen. It takes a cell within the filtered column and returns the filter criteria.

Function FilterCriteria(oRng As Range) As String
Dim sFilter As String
On Error GoTo NoMoreCriteria
With oRng.Parent.AutoFilter
'Is it in the AutoFilter range?
If Intersect(oRng, .Range) Is Nothing Then _
GoTo NoMoreCriteria
'Get the filter object for the appropriate column
With .Filters(oRng.Column - .Range.Column + 1)
'Does this column have an AutoFilter criteria?
If Not .On Then GoTo NoMoreCriteria
'It has one!
sFilter = .Criteria1
'Does it have another (i.e. the "Custom" filter)?
Select Case .Operator
Case xlAnd
sFilter = sFilter & " AND " & .Criteria2
Case xlOr
sFilter = sFilter & " OR " & .Criteria2
End Select
End With
End With
FilterCriteria = sFilter
End Function