Fining Min & Max Values of Select (Filtered?) Rows

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,562
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I need to get the min and max values of the series of rows where the value in column C = "textvalue"

What I attempted, was to apply a filter to the data to isolate those rows in which column C = "textvalue". I then created the formulae to determine the min value from column P, and max value from column P.

Turns out, I got the min and max values of the entire database ... not the isolated rows. How can I determine (VBA) the min and max values of only those select rows.

Code:
Sub Wroxeter()
    Dim wshcore As Worksheet
    
    Set wshcore = Worksheets("CONTROL_1")
    
    'Excelsior
    With wshcore
        llastrow = wshcore.Range("A" & Rows.Count).End(xlUp).Row
        .AutoFilterMode = False
        .Range("A1:EH" & llastrow).AutoFilter
        .Range("A1:EH" & llastrow).AutoFilter Field:=8, Criteria1:="TextValue"
        lwmin = Application.Min(.Range("N:N"))
        lwmax = Application.Max(.Range("O:O"))
        MsgBox "Excelsior" & Chr(13) & "       Minimum: " & Format(lwmin, "h:mm AM/PM") & Chr(13) & "       Maximum: " & Format(lwmax, "h:mm AM/PM")
    End With
End Sub
 
Last edited:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
If you want min and max from column P with a filter applied:
Code:
lwmin = WorksheetFunction.Subtotal(5, Range("P:P"))
lwmax= WorksheetFunction.Subtotal(4, Range("P:P"))
 
Upvote 0
min from column N, and max from O, based on a filtering of column P.
I think I have an idea what needs to be changed, but can't put my finger on what "4" and "5" represent.
 
Upvote 0
min from column N, and max from O, based on a filtering of column P.
I think I have an idea what needs to be changed, but can't put my finger on what "4" and "5" represent.
Take a look at the worksheet function SUBTOTAL in Excel (not VBA) Help.

Your original post stated : "I then created the formulae to determine the min value from column P, and max value from column P." Obviously, you can replace P:P with N:N for the min and with O:O for the max if that's what you want.
 
Upvote 0
Yes ... I made a typo and figured out the O and P changes. I discovered the 4 and 5 thing shortly after I made my post. (but after my ability to edit).

Thanks JoeMo.
 
Upvote 0
Can we take this one step further? How can I determine which row these min and max values reside in?
 
Upvote 0
Can we take this one step further? How can I determine which row these min and max values reside in?

Use Application.Match(lwmin,Range("N:N"),0) to get the row number.
 
Upvote 0
Hello all,

I added Joe's code and it results in the row position relative to the filtered database. Ie. the filtered database is 2 rows ... 11 (which is holding the min value) and 13 (which is holding the max value). Joe's code gives me row positions 2 and 3, rather than the row number ... 11 and 13 ... required.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top