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

[Ark68]

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.

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

 

[JoeMo]

If you want min and max from column P with a filter applied:

lwmin = WorksheetFunction.Subtotal(5, Range("P:P"))
lwmax= WorksheetFunction.Subtotal(4, Range("P:P"))

 

[Ark68]

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.

 

[JoeMo]

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.

 

[Ark68]

ADVERTISEMENT

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.

 

[Ark68]

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

 

[JoeMo]

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.

 

[Ark68]

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.