ExcelVBA: Find max Value in filtered range

What VBA would I need so that it will find a maximum value from a filtered worksheet.
(See Example Below...)

Pretend I have a worksheet with data that looks like below... (Autofilters turned on)

Column     A                 B
Row1:    Names          Age
Row2:    John             20
Row3:    Mat               24
Row4:    John             28
Row5:    Matt              15
Row6:    John              22

Pretend the user autofilters Colum A("Names") to only show rows with "John".
(Only 3 rows will show in this case because there are 3 rows with "John" in Column A.)

When a user presses a button on this worksheet a message will display saying the following:

"The oldest person in the filtered range is 28"

Please submit the fastest code to accomplish this task. (i.e. if the user had 40,000 rows, it could do this task quickly)
ouestqueAsked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
Rather,

MsgBox "The oldest person in the filtered range is " & _
WorksheetFunction.Max(Intersect(UsedRange, Range("B:B").SpecialCells(xlCellTypeVisible)))
0
 
cyberkiwiCommented:
MsgBox WorksheetFunction.Max(Intersect(UsedRange, Range("B:B").SpecialCells(xlCellTypeVisible)))
0
 
FernandoFernandesConnect With a Mentor Commented:
you have to use the SUBTOTAL function...
just right this in any cell out of the filtered area (preferentially above it...)

=SUBTOTAL(4,B:B)
0
 
FernandoFernandesConnect With a Mentor Commented:
if you need it to be part of a macro:

dim strMsg as tring
strMsg = Application.WorksheetFunction.Subtotal(4,Range("B:B"))
msgbox strMsg

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.