Finding Last Row of a Filtered Range

[RMcCreavy]

Bit embarrassed I can't figure this one out! Swear it was working when I originally tested the macro, but now the Last Row variable on this filtered range keeps returning either "1" or the unfiltered last row.

Have tried many combinations with and without SpecialCells(xlCellTypeVisible).

Code: Select all

Option Explicit
Dim LastRow as Long, LastColumn as Long, Table_Range as Range, FoundCell as Range

LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
LastColumn = ActiveSheet.Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column

Table_Range = ActiveSheet.Range(Cells(1, 1), Cells(LastRow, LastColumn))

With Table_Range
.AutoFilter Field:=LastColumn, Criteria1:="0"
End With

'Some code I have tried both within the With statement above and afterwards
'LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
'LastRow  = ActiveSheet.UsedRange.SpecialCells(xlCellTypeVisible).Row
'LastRow  = Table_Range.SpecialCells(xlCellTypeVisible).Rows.Count

'LastRow = 0
'For Each FoundCell In Table_Range.SpecialCells(xlCellTypeVisible)
'    If FoundCell.Row > LastRow  Then LastRow  = FoundCell.Row
'Next FoundCell

 'Set FoundCell= .Find(What:="*", After:=.Cells(.Rows.Count), _
                              LookIn:=xlFormulas, LookAt:=xlPart, _
                              SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
                              MatchCase:=False)
'        If Not FoundCells Nothing Then
'            LastRow= FoundCell.Row
' 	End If

[HansV]

What kind of data does column A contain? Text, numbers, dates, a mixture...?