Find max and minimum values in a section of a column, then do the same in next section.

Hi

You can solve that without VBA.
Suppose your data in M2:M25
Use two columns P and Q (for example) as auxiliary columns. P records the start row of each group and Q the row where it ends
In P2 use and drag down the following formula

Formula:

=IFERROR(AGGREGATE(15,6,ROW($A$1:$A$25)/ABS(($M$1:$M$25<>"")-1),ROW(A1))+1,"")

In Q2 use and drag down

Formula:

=IF(P3="","",P3-2)

To get the maximum use

Formula:

=IF($Q2="","",MAX(INDEX(INDEX($M:$M,$P2,0):INDEX($M:$M,$Q2,0),0)))

To get the minimum use

Formula:

=IF($Q2="","",MIN(INDEX(INDEX($M:$M,$P2,0):INDEX($M:$M,$Q2,0),0)))

See the file for clarification.

VBA

Sub test()
    Dim myArea As Range
    Columns("b:c").NumberFormat = "yyyy-mm-dd hh:mm"
    [b1:c1] = [{"min","max"}]
    For Each myArea In Range("a2", Range("a" & Rows.Count).End(xlUp)).SpecialCells(2).Areas
        myArea(1, 2).Resize(, 2).Value = Array(Evaluate("min(" & myArea.Address & "+0)"), Evaluate("max(" & myArea.Address & "+0)"))
    Next
End Sub

That did it, thanks!

Originally Posted by jindon

VBA

Sub test()
    Dim myArea As Range
    Columns("b:c").NumberFormat = "yyyy-mm-dd hh:mm"
    [b1:c1] = [{"min","max"}]
    For Each myArea In Range("a2", Range("a" & Rows.Count).End(xlUp)).SpecialCells(2).Areas
        myArea(1, 2).Resize(, 2).Value = Array(Evaluate("min(" & myArea.Address & "+0)"), Evaluate("max(" & myArea.Address & "+0)"))
    Next
End Sub

That did it, thanks!

You are welcome and thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.