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.

 

[Jobowo]

Hmm. I finally had a look at jbeaucaire's referenced code. It does the same as mine but mine doesn't automatically refresh so that may be what the Application.volatile achieves. Anyway, here is that code 'doctored' to achieve your desired goal. Note the comments (with single ' at beginning of line)

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 .On Then
               ' Criteria 1 without = sign
               strCri1 = Right(.Criteria1, Len(.Criteria1) - 1)
               ' Remove from here....
               If .Operator = xlAnd Then
                  strCri2 = " AND " & .Criteria2
               ElseIf .Operator = xlOr Then
                  strCri2 = " OR " & .Criteria2
               ' ...to here if there is no criteria 2
               ' and remove "& strCri2' from the lines below
               End If
              AutoFilter_Criteria = UCase(Header) & strCri1 & strCri2
              ' or without field description:
              ' AutoFilter_Criteria = strCri1 & strCri2
           End if
        End With
    End With
 End Function

 

[omairhe]

appreciate it Sir...

will let u know if any problems at all

 

[omairhe]

hey working just like expected
Thanks for kind effort