Use a target cell value to filter

SanjayGMusafir

Well-known Member
Joined
Sep 7, 2018
Messages
1,339
Office Version
  1. 2021
Platform
  1. MacOS
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 ?

Rich (BB 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
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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
 
Upvote 0
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


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
 
Upvote 0
Add the msgbox
Code:
Sub IIVRglobal()
   Dim Fltr As Range
   Set Fltr = Cells(Rows.Count, Range("Visits[[#headers],[SR]]").Column).End(xlUp)
   [COLOR=#0000ff]MsgBox Fltr.Value & vbLf & Fltr.Column[/COLOR]
   ActiveSheet.ListObjects("Visits").Range.AutoFilter Fltr.Column, Fltr.Value
End Sub
What does it say?
 
Upvote 0
Thanks Fluff for writing back. Surprisingly got no message...
Rich (BB 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
Add the msgbox
Code:
Sub IIVRglobal()
   Dim Fltr As Range
   Set Fltr = Cells(Rows.Count, Range("Visits[[#headers],[SR]]").Column).End(xlUp)
   [COLOR=#0000ff]MsgBox Fltr.Value & vbLf & Fltr.Column[/COLOR]
   ActiveSheet.ListObjects("Visits").Range.AutoFilter Fltr.Column, Fltr.Value
End Sub
What does it say?
 
Upvote 0
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

Add the msgbox
Code:
Sub IIVRglobal()
   Dim Fltr As Range
   Set Fltr = Cells(Rows.Count, Range("Visits[[#headers],[SR]]").Column).End(xlUp)
   [COLOR=#0000ff]MsgBox Fltr.Value & vbLf & Fltr.Column[/COLOR]
   ActiveSheet.ListObjects("Visits").Range.AutoFilter Fltr.Column, Fltr.Value
End Sub
What does it say?
 
Last edited:
Upvote 0
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:
Upvote 0
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...

Rich (BB 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


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:
Upvote 0
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
 
Upvote 0
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


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

Have you Changed every instance of Fltr to varSr?
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top