AutoFilter

[omairhe]

Hi
i've been using Data|Filter for quiet some time now. by clicking on Advanced filter it is showing me that

List range: $C$60:$Y$8660

this is correct and it works fine. heres the thing that i want when in U60 i select a filter arrow and uncheck all then select a specific name to filter, that one name that i check for filtering should also be copied to Cell U3. the rest remains the same with filtering and its function....Is Possible?

Thank you.

 

[jbeaucaire]

That's not a built-in function, but you can easily add it with a User-Defined Function (UDF).

Try this:
http://www.ozgrid.com/VBA/autofilter-criteria.htm

 

[omairhe]

http://www.ozgrid.com/VBA/autofilter-criteria.htm

Thanks its very close to what i want but only if the headings eg. YEAR: =, DEPT: = and AMOUNT: were not shown but an exact match of the selected name was up there. if i remove the heading it still shows an " := " sign. any way around this?

Thanks again..

 

[Jobowo]

So you're saying that when you go to the filter and select a value to filter on, you want that value saved in U3?

Can't be done without a macro since the filter criteria are not available through worksheet functions. You could use a macro to access the value...

ActiveSheet.AutoFilter.Filters(19).Criteria1 etc

but since filtering doesn't trigger any of the usual worksheet events, you would need to click a button to save the value. Doesn't sound as though this is what you are looking for.

 

[Jobowo]

Oops! Looks as though there have been a few replies while I was messing with this.

 

[omairhe]

perhaps if the vba code on that link u gave me can be altered in some way to allow this...

help will be appreciated

 

[Jobowo]

Place this code in a module. Assign "getFilter" to a macro button.

Public Function getFilter()
    With ActiveSheet
        .Cells(3, 21) = .AutoFilter.Filters(19).Criteria1
    End With
End Function

 

[omairhe]

Hi. do i need to keep that other VBA too??? i noticed that i removed semi colons from that VBA and it also removed from the worksheet so now only " = "sign is there which i like to remove

 

[omairhe]

Function AutoFilter_Criteria(Header As Range) As String

Dim strCri1 As String, strCri2 As String



Application.Volatile



With Header.Parent.AutoFilter

With .Filters(Header.Column - .Range.Column + 1)



If Not .On Then Exit Function



strCri1 = .Criteria1

If .Operator = xlAnd Then

strCri2 = " AND " & .Criteria2

ElseIf .Operator = xlOr Then

strCri2 = " OR " & .Criteria2

End If



End With

End With



AutoFilter_Criteria = UCase(Header) & ": " & strCri1 & strCri2

End Function



in this code i removed : semi colon and it disappeared in worksheet
now only if i could remove = sign
thanks in advance

 

[omairhe]

Place this code in a module. Assign "getFilter" to a macro button.

Public Function getFilter()
    With ActiveSheet
        .Cells(3, 21) = .AutoFilter.Filters(19).Criteria1
    End With
End Function

thanks for the effort but a macro button is not the best option to me