AutoFilter

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

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..

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.

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

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

help will be appreciated

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

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

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

Jobowo said:

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

Code:

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

Click to expand...

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