Solved

Find Min & Max VBA

Posted on 2013-01-04

3 solutions

741 Views

Last Modified: 2013-01-05

Hello,

I have a set of numbers in column B like below:
43
432342
432
342
34234324
534
432
3
443
543
432
4453
34
323243

I need the best way via VBA to find the Maximum and minimum of this set? How to do it?

Thanks

Comment

Question by:Rayne

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

6 Comments

Author Comment

by:Rayne

ID: 387463072013-01-04

Also, all the numbers in that set, will be of same number of digits, just different

thanks

LVL 82

Assisted Solution

by:David Johnson, CD, MVP

David Johnson, CD, MVP earned 75 total points

ID: 387463292013-01-05

@min(range)
@max(range)

LVL 48

Assisted Solution

by:Martin Liss

Martin Liss earned 125 total points

ID: 387466392013-01-05

ve3ofa meant =min and =max so for example if the data is in column A starting in A1

=MIN(A1:A12)
=MAX(A1:A12)

LVL 17

Accepted Solution

by:andrewssd3

andrewssd3 earned 300 total points

ID: 387466842013-01-05

The question was asking about VBA, so this illustrates a couple of ways of doing it with VBA:

Public Sub MaxInCol()

    Dim dMaxVal As Double
    
    
    ' simple version if you just want the whole column
    dMaxVal = Application.WorksheetFunction.Max(Range("B:B"))


    ' more complicated to limit to certain data
    Dim rngDataStart As Range
    Dim rngDataEnd As Range
    
    Set rngDataStart = Range("B2")
    ' find  the last cell with data in col B
    Set rngDataEnd = Cells(ActiveSheet.Rows.Count, 2).End(xlUp)

    dMaxVal = Application.WorksheetFunction.Max(Range(rngDataStart, rngDataEnd))
    
    Stop

End Sub

Open in new window

Obviously for min just use Application.WorksheetFunction.Min

Author Closing Comment

by:Rayne

ID: 387471002013-01-05

Thanks everyone :) for your excellent help. I badly needed this

R

LVL 48

Expert Comment

by:Martin Liss

ID: 387471952013-01-05

You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Install Microsoft Office Picture Manager in Office 2016/Office 365

Article by: Joe

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …