PDA

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



Aussiebear
02-19-2010, 07:48 AM
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
02-19-2010, 10:57 AM
I am assuming that you have the coding done for the AutoFilter:

Does this help?


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

Philcjr
02-19-2010, 11:01 AM
or if you only want to zero in on G11 to LastRow


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

Bob Phillips
02-19-2010, 11:59 AM
Max: =SUBTOTAL(104,OFFSET(G11,,,COUNTA(G:G)-COUNTA(G1:G10),1))

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