AutoFilter a Dynamic Range

[julhs]

I need help completing the conversion of my existing AutoFilter code to work for me on a Dynamic Range.
(Have set .EnableAutoFilter = True in “ThisWorkbook” and the Change Event is in the Sheet module)
I can’t get the section after the last “Else” right, it just doesn’t do the filtering (in the converted version)
(Screen shot image at bottom)
My existing code that works

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Address = "$AU$410" Then
     If Range("AU410") = "All" Then
        Range("AQ414").AutoFilter
Else
        Range("AQ414").AutoFilter Field:=3, Criteria1:=Range("AU410")
   End If
  End If
End Sub

Conversion to Dynamic Range

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sht As Worksheet
Dim rng As Range
Dim FrwD As Long
Dim LrwD As Long
Dim Dtls As Range

Set sht = ThisWorkbook.ActiveSheet
Set rng = sht.Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole)
        FrwD = rng.Row
        LrwD = sht.Cells(sht.Rows.Count, "As").End(xlUp).Row
'-------------------------------
Debug.Print "rng:" & Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole).Address
Debug.Print "rng.Row: " & rng.Row
Debug.Print Target
Debug.Print "Target: " & Target.Address
Debug.Print FrwD
Debug.Print LrwD
         '[B][U]Debug Results[/U][/B]
               'rng:$T$412
               'rng.Row: 412
               'To A
               'Target: $AU$410
               '412
               '421
'------------------------------------------------
Set Target = sht.Range("AU" & FrwD - 2)
Set Dtls = sht.Range("AQ" & FrwD + 2)
         If Target = 0 Then
                MsgBox Target.Address(0, 0) & " NO Target in Cell"
    Else
         If Dtls = 0 Then
                MsgBox Dtls.Address(0, 0) & " NO Details in Cell"
         If Target.Address = "Target" Then
         If Target = "All" Then
               Dtls = AutoFilter
               Dtls.AutoFilter Field:=3, Criteria1:=Target
'-------------------------------
Debug.Print Dtls
Debug.Print Dtls.Address
'-------------------------------
     End If
    End If
   End If
  End If
End Sub

 

[julhs]

Via the most convoluted route possible, I have a WORKING version that can deal with a dynamic range!!

But ONLY after help from UMPTEEN posts on various different related aspects; to ALL those board regulars that have helped me with this, a BIG thank you. (TO many to individually name, but if they read this they may well see the connection)

So this is the WORKING version of combing/adapting the 2 separate codes.

Option Explicit

'###### Filtering on a Dynamic range via "DataValidation" list on the ActiveSheet

Private Sub Worksheet_Change(ByVal Target As Range)
              Application.ScreenUpdating = False

Dim sht As Worksheet
Dim rng As Range
Dim FrwD As Long
Dim BFLtr As Range
Dim BTgt1 As Range
Dim BTgt2 As Range

Set sht = ThisWorkbook.ActiveSheet
Set rng = sht.Range("T:T").Find(what:="Cash Paid", LookIn:=xlValues, LookAt:=xlWhole)
                FrwD = rng.Row      'Establishes first row of the dynamic range
Set BFLtr = Range("AQ" & FrwD + 2)  'Is 1st cell of range to filter (BFLtr)
Set BTgt1 = Range("AU" & FrwD - 2)  'Is a defined/limited DataValidation list to select to filter for (BTgt1)
Set BTgt2 = Range("AV" & FrwD - 2)  'Is the complete DataValidation list to select to filter for (BTgt2) (the results of a previous/independent Sub)

       If BFLtr = 0 Then
          ActiveSheet.AutoFilterMode = False          'Deactivates filter if filter range is empty
  Else
       If Target.Address = BTgt1.Address Then
       If BTgt1 = "All Details" Then
            BFLtr.AutoFilter
  Else
           BFLtr.AutoFilter field:=3, Criteria1:=BTgt1
        End If
      End If

      If BFLtr = 0 Then
         ActiveSheet.AutoFilterMode = False          'Deactivates filter if filter range is empty
  Else
      If Target.Address = BTgt2.Address Then
      If BTgt2 = "All Details" Then
         BFLtr.AutoFilter
  Else
         BFLtr.AutoFilter field:=3, Criteria1:=BTgt2
     End If
    End If
   End If
  End If
          Application.ScreenUpdating = True
End Sub