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))
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.