Max date in auto-filtered range
 

Hi.

I have a column of roughly 20,000 dates.

There are other fields, upon which some autofilters are applied.

When those filtering criteria are applied, I need to obtain the maximum date
of the visible dates that are left...

I put in a formula =if(subtotal(3,rgDateRange),<dateAddress,0) formula to
identify the values of the dates that are visible, but I'm not sure that's
needed.

I also think an array formula could do it, but I could use some more
experience with that.

Optimally, I only need to know one value from the visible range... the
maximum one.

Suggestions? Thanks.

Max date in auto-filtered range
 

wouldn't the subtotal function work?

=SUBTOTAL(104,Your date range)

--


Gary


"mark" wrote in message
...
Hi.

I have a column of roughly 20,000 dates.

There are other fields, upon which some autofilters are applied.

When those filtering criteria are applied, I need to obtain the maximum
date
of the visible dates that are left...

I put in a formula =if(subtotal(3,rgDateRange),<dateAddress,0) formula
to
identify the values of the dates that are visible, but I'm not sure that's
needed.

I also think an array formula could do it, but I could use some more
experience with that.

Optimally, I only need to know one value from the visible range... the
maximum one.

Suggestions? Thanks.

Max date in auto-filtered range
 

Got it, I think.

This seems to do what I want:

Application.WorksheetFunction.Max(Range("rgDates") .SpecialCells(xlCellTypeVisible))

Max date in auto-filtered range
 

wouldn't the subtotal function work?
=SUBTOTAL(104,Your date range)


Thanks, Gary. You're right, it does.

I tended only to use =subtotal(3,range) and =subtotal(9,range)

Thanks for directing me to this one.

Mark