VBA - Find and filter last column

[leatherhen99]

Good afternoon and a happy new year!

I'm creating a macro that will look for the column and filter... the only problem is, there could be 14 columns one run and there could be 12 the next (it could range from more or less), but it'll always be in the last column... but when I create the code, it always has "field:=8"... but that's not always going to be the last column... I haven't found any workarounds for this... can anyone help???

These are the two that I've tried...but if the field number changes, how do i get the macro to select the correct field to filter???

Within a table:
Sub lastcol()
'
' lastcol Macro
ActiveCell.Offset(-14, 0).Range("A1").Select
Selection.End(xlToRight).Select
ActiveSheet.ListObjects("CalEvents").Range.AutoFilter Field:=8, Criteria1:= _
"0:00:00"
End Sub

within a range:
Sub Filter_Last()
' Filter_Last Macro
'
ActiveCell.Offset(-11, -2).Range("A1").Select
Selection.End(xlToRight).Select
Selection.AutoFilter
ActiveSheet.Range("$A$3:$H$29").AutoFilter Field:=8, Criteria1:="0:00:00"
End Sub

Thanks!
Heather

 

[Fluff]

How about

   With ActiveSheet.ListObjects("CalEvents")
      .Range.AutoFilter .ListColumns.Count, "0:00:00"
   End With

 

[My Aswer Is This]

You can do it like this:

Dim ans As Long
ans = ActiveSheet.ListObjects("CalEvents").ListColumns.Count
ActiveSheet.ListObjects("CalEvents").Range.AutoFilter Field:=ans, Criteria1:="0:00:00"