Solved: Find the min or Max value in an autofiltered range

[Aussiebear]

I am trying to find by formula or vba, the earliest and or latest date in a autofiltered range with the data residing in cells G11 to last row.

[Philcjr]

I am assuming that you have the coding done for the AutoFilter:

Does this help?

[vba]
Dim EarliestDate As Date, LatestDate As Date

With Application.WorksheetFunction
EarliestDate = .Min(Range("G:G").SpecialCells(xlCellTypeVisible))
LatestDate = .Max(Range("G:G").SpecialCells(xlCellTypeVisible))
End With

[/vba]

[Philcjr]

or if you only want to zero in on G11 to LastRow

[vba]
Dim LastRow As Long, EarliestDate As Date, LatestDate As Date

LastRow = Cells(Rows.Count, "G").End(xlUp).Row

With Application.WorksheetFunction
EarliestDate = .Min(Range("G11:G" & LastRow).SpecialCells(xlCellTypeVisible))
LatestDate = .Max(Range("G11:G" & LastRow).SpecialCells(xlCellTypeVisible))
End With
[/vba]

[Bob Phillips]

Max: =SUBTOTAL(104,OFFSET(G11,,,COUNTA(G:G)-COUNTA(G1:G10),1))

Min: =SUBTOTAL(105,OFFSET(G11,,,COUNTA(G:G)-COUNTA(G1:G10),1))