Use a target cell value to filter
Results 1 to 10 of 10

Thread: Use a target cell value to filter
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2018
    Location
    New Delhi, India
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Use a target cell value to filter

    Dear Expert

    I have to filter my worksheet quite frequently on the basis of value in the last cell of a column. Please help me form a vba for that. I'm sharing the amount of work I was able to do... Thanks a lot 🙏

    Code:
    Range("Visits[[#Headers],[Sr]]").Select
        
        Dim varSr As String
        
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlUp).Select
        
        varSr = Target.Value.Copy
        
    'to use value of target cell - This thing not working    
    ActiveSheet.ListObjects("Visits").Range.AutoFilter Field:=1, Criteria1:=varSr, Operator:=xlAnd
        
    'Old vba that was working well, but wanted to replace Cell address E1 with a target value    
    'ActiveSheet.ListObjects("Visits").Range.AutoFilter Field:=1, Criteria1:= _
            Range("E1").Value, Operator:=xlAnd
    

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,058
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

    Default Re: Use a target cell value to filter

    How about
    Code:
       Dim Fltr As Range
       Set Fltr = Cells(Rows.Count, Range("Visits[[#headers],[SR]]").Column).End(xlUp)
       ActiveSheet.ListObjects("Visits").Range.AutoFilter Fltr.Column, Fltr.Value
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Sep 2018
    Location
    New Delhi, India
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use a target cell value to filter

    Thanks for replying Fluff. Somehow, it didn't work. I tried both xlUp & xlDown. It is not filtering... Am I missing some trick?

    Code:
    Dim varSr As Range
        
    Set varSr = Cells(rows.Count, Range("Visits[[#Headers],[SR]]").Column).End(xlUp)
    ActiveSheet.ListObjects("Visits").Range.AutoFilter Fltr.Column, Fltr.Value

    Quote Originally Posted by Fluff View Post
    How about
    Code:
       Dim Fltr As Range
       Set Fltr = Cells(Rows.Count, Range("Visits[[#headers],[SR]]").Column).End(xlUp)
       ActiveSheet.ListObjects("Visits").Range.AutoFilter Fltr.Column, Fltr.Value

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,058
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

    Default Re: Use a target cell value to filter

    Add the msgbox
    Code:
    Sub IIVRglobal()
       Dim Fltr As Range
       Set Fltr = Cells(Rows.Count, Range("Visits[[#headers],[SR]]").Column).End(xlUp)
       MsgBox Fltr.Value & vbLf & Fltr.Column
       ActiveSheet.ListObjects("Visits").Range.AutoFilter Fltr.Column, Fltr.Value
    End Sub
    What does it say?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Sep 2018
    Location
    New Delhi, India
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use a target cell value to filter

    Thanks Fluff for writing back. Surprisingly got no message...
    Code:
    Dim varSr As Range
        
    Set varSr = Cells(rows.Count, Range("Visits[[#Headers],[SR]]").Column).End(xlUp)
        MsgBox Fltr.Value & vbLf & Fltr.Column
        ActiveSheet.ListObjects("Visits").Range.AutoFilter Fltr.Column, Fltr.Value
    Quote Originally Posted by Fluff View Post
    Add the msgbox
    Code:
    Sub IIVRglobal()
       Dim Fltr As Range
       Set Fltr = Cells(Rows.Count, Range("Visits[[#headers],[SR]]").Column).End(xlUp)
       MsgBox Fltr.Value & vbLf & Fltr.Column
       ActiveSheet.ListObjects("Visits").Range.AutoFilter Fltr.Column, Fltr.Value
    End Sub
    What does it say?

  6. #6
    Board Regular
    Join Date
    Sep 2018
    Location
    New Delhi, India
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use a target cell value to filter

    My mistake Fluff. I used it as a seperate VBA & Got Message...

    A 2254
    1

    "A 2254" is the value I wanted to use as filter, what this "1" is I can't understand...

    And this time it filtered as intended... But is not working as part of bigger vba...

    Please help

    Quote Originally Posted by Fluff View Post
    Add the msgbox
    Code:
    Sub IIVRglobal()
       Dim Fltr As Range
       Set Fltr = Cells(Rows.Count, Range("Visits[[#headers],[SR]]").Column).End(xlUp)
       MsgBox Fltr.Value & vbLf & Fltr.Column
       ActiveSheet.ListObjects("Visits").Range.AutoFilter Fltr.Column, Fltr.Value
    End Sub
    What does it say?
    Last edited by SanjayGulatiMusafir; Jul 17th, 2019 at 03:29 PM.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,058
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

    Default Re: Use a target cell value to filter

    1 is the column that contains the value & the column that will be filtered.

    Have you Changed every instance of Fltr to varSr?
    Last edited by Fluff; Jul 17th, 2019 at 03:30 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    Board Regular
    Join Date
    Sep 2018
    Location
    New Delhi, India
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use a target cell value to filter

    Thanks for your support Fluff. I am quoting my entire vba code here. Can you help me identify where the mistake is... Honestly I'm a novice and work with small information I gather over net...

    Code:
    Sub VisitsFilter()
    '
    ' VisitsFilter Macro
    '
    
    
    '
        Application.Calculate
        
        Range("Visits[#All]").Select
        
        ActiveSheet.ShowAllData
        
        ActiveSheet.AutoFilterMode = False
    
    
        On Error Resume Next
        ActiveSheet.ShowAllData
        Err.Clear
    
    
        ActiveWorkbook.Worksheets("Visits").ListObjects("Visits").Sort.SortFields.Clear
        
        ActiveWorkbook.Worksheets("Visits").ListObjects("Visits").Sort.SortFields.Add _
            Key:=Range("Visits[V Dt]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets("Visits").ListObjects("Visits").Sort.SortFields.Add _
            Key:=Range("Visits[V Tm]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets("Visits").ListObjects("Visits").Sort.SortFields.Add _
            Key:=Range("Visits[Sr]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("Visits").ListObjects("Visits").Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        Range("Visits[[#Headers],[Sr]]").Select
        
        Dim varSr As Range
    
        'Selection.End(xlDown).Select
        'Selection.End(xlDown).Select
        'Selection.End(xlUp).Select
    
        Set varSr = Cells(rows.Count, Range("Visits[[#Headers],[SR]]").Column).End(xlUp)
        'MsgBox Fltr.Value & vbLf & Fltr.Column
        ActiveSheet.ListObjects("Visits").Range.AutoFilter Fltr.Column, Fltr.Value
    
    'ActiveSheet.ListObjects("Visits").Range.AutoFilter Field:=1, Criteria1:=varSr, Operator:=xlAnd
        
        'ActiveSheet.ListObjects("Visits").Range.AutoFilter Field:=1, Criteria1:= _
            Range("E1").Value, Operator:=xlAnd
        
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlUp).Select
        
        ActiveWorkbook.Save
       
    End Sub

    Quote Originally Posted by Fluff View Post
    1 is the column that contains the value & the column that will be filtered.

    Have you Changed every instance of Fltr to varSr?
    Last edited by SanjayGulatiMusafir; Jul 17th, 2019 at 03:39 PM.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,058
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

    Default Re: Use a target cell value to filter

    Try
    Code:
    Sub VisitsFilter()
    '
    ' VisitsFilter Macro
    '
        Application.Calculate
        Dim varSr As Range
        
       With Sheets("Visits")
          If .FilterMode Then .ShowAllData
          
          
          With .ListObjects("Visits").Sort
             .SortFields.Clear
             .SortFields.Add _
                Key:=Range("Visits[V Dt]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                DataOption:=xlSortNormal
             .SortFields.Add _
                Key:=Range("Visits[V Tm]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                DataOption:=xlSortNormal
             .SortFields.Add _
                Key:=Range("Visits[Sr]"), SortOn:=xlSortOnValues, Order:=xlAscending, _
                DataOption:=xlSortNormal
             .Header = xlYes
             .MatchCase = False
             .Orientation = xlTopToBottom
             .SortMethod = xlPinYin
             .Apply
          End With
          Set varSr = .Cells(Rows.Count, Range("Visits[[#Headers],[SR]]").Column).End(xlUp)
          .ListObjects("Visits").Range.AutoFilter varSr.Column, varSr.Value
       End With
        ActiveWorkbook.Save
       
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    Board Regular
    Join Date
    Sep 2018
    Location
    New Delhi, India
    Posts
    62
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Use a target cell value to filter

    Aah Fluff. I just changed my code as below and it did the magic for me in a fast and efficient way... Thanks a lot ��
    Code:
    Sub IIVRglobal()
       Dim Fltr As Range
       Set Fltr = Cells(rows.Count, Range("Visits[[#headers],[SR]]").Column).End(xlUp)
       'MsgBox Fltr.Value & vbLf & Fltr.Column
       ActiveSheet.ListObjects("Visits").Range.AutoFilter Fltr.Column, Fltr.Value
       Range("Visits[[#Headers],[Sr]]").Select
        
        Selection.End(xlDown).Select
        Selection.End(xlDown).Select
        Selection.End(xlUp).Select
        
    End Sub

    Quote Originally Posted by Fluff View Post
    1 is the column that contains the value & the column that will be filtered.

    Have you Changed every instance of Fltr to varSr?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •