Use a target cell value to filter

[SanjayGMusafir]

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 ?

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

 

[Fluff]

How about

   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

 

[SanjayGMusafir]

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

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

   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

 

[Fluff]

Add the msgbox

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?

 

[SanjayGMusafir]

Thanks Fluff for writing back. Surprisingly got no message...

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

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?

 

[SanjayGMusafir]

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

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?

 

[Fluff]

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

Have you Changed every instance of Fltr to varSr?

 

[SanjayGMusafir]

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

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?

 

[Fluff]

Try

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

 

[SanjayGMusafir]

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

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?