Find Max One Column Multiple Row Ranges

Hello Experts:

Looking for a VBA script that will find the maximum value for a range of values in one column and repeat this between breaks.  The breaks are not uniform meaning that there may be 2-3 empty rows before the next set is found.  There may be even just one value in that set.  I'd like to display the maximum value in column B in the cell adjacent to the max value in column A.

Col A     Col B (Max Values)

1
2
3
4
5
15         15
9
8
10

5
6
7
12
45        45
31
23
8
6


1
4         4
2

15      15
donisanpAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
Works for me. I added a command button to make the test easier.

Kevin
Experts-MaxTest.xls
0
 
zorvek (Kevin Jones)ConsultantCommented:
Use this macro with the sheet to be evaluated active:

Public Sub AddMaxValues()

   Dim Target As Range
   
   With ActiveSheet
      Set Target = .Range(.[A1], .[A1].End(xlDown))
      Do
         Target.Offset(Application.Match(Application.Max(Target), Target, 0) - 1, 1).Resize(1, 1) = Application.Max(Target)
         If Target.Row + Target.Rows.Count > .Rows.Count Then Exit Do
         Set Target = Target.Offset(Target.Rows.Count).Resize(1).End(xlDown)
         Set Target = .Range(Target, Target.End(xlDown))
      Loop
   End With

End Sub

Kevin
0
 
donisanpAuthor Commented:
I'm getting a "data type mismatch" error
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
zorvek (Kevin Jones)ConsultantCommented:
Then there is a discrepancy between what you posted in your question and what you are actually working with. Please post your workbook.

Kevin
0
 
donisanpAuthor Commented:
I've attached the file.  So in column B I'd like the macro to output the max value for each run.
Experts-MaxTest.xls
0
 
donisanpAuthor Commented:
Brilliant!  Exactly what I needed, thank you very much!
0
All Courses

From novice to tech pro — start learning today.