Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1823
  • Last Modified:

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)
0
ouestque
Asked:
ouestque
  • 2
  • 2
3 Solutions
 
cyberkiwiCommented:
MsgBox WorksheetFunction.Max(Intersect(UsedRange, Range("B:B").SpecialCells(xlCellTypeVisible)))
0
 
cyberkiwiCommented:
Rather,

MsgBox "The oldest person in the filtered range is " & _
WorksheetFunction.Max(Intersect(UsedRange, Range("B:B").SpecialCells(xlCellTypeVisible)))
0
 
FernandoFernandesCommented:
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
 
FernandoFernandesCommented:
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now