AutoFilter

  • Thread starter Thread starter Cheng Liu
  • Start date Start date
C

Cheng Liu

Any one knows if AutoFilter returns value for selected
item in the list? I need to reference the selected item
but the row number changes based on what one had
selected.
Say if we select "New York", the colome display "New
York". But I have not way to reference it in other
spreadhsheet due to rows contain New York can be any
number.
Any help will be greatly appreciated.
If the solution is in VBasic, that is fine also.

Thanks,
 
Here's a non-VBA way. Assuming that the filtered column is
A and headers are in row 1, use the formula:

=INDEX(A1:A10,MAX(ROW(A1:A10)*SUBTOTAL(3,OFFSET(A1:A10,ROW
(A1:A10)-MIN(ROW(A1:A10)),,1))))

Array-entered, meaning press ctrl/shift/enter.

Expand the range beyond A10 if needed.

HTH
Jason
Atlanta, GA
 
If you want to show the value that has been selected in the AutoFilter
dropdown, you can create a User Defined Function. Tom Ogilvy posted the
following function, that can be used to return the criteria from a
column in an autofiltered table. It will show both criteria if there are
two, and will include the operator.

Public Function ShowFilter(rng As Range)
'UDF that displays the filter criteria.
'posted by Tom Ogilvy 1/17/02
'To make it respond to a filter change, tie it to the subtotal command.
'=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32)
'So the above would show the criteria for column B

Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet
Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range

If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function
'==============================================
 
Back
Top