Find max and min value in range of cells using VBA

**proepert** · 06-06-2010, 09:53 PM

I have this formula in Excel:

Please Login or Register  to view this content.

I need to move this to VBA code for other reasons. Is there a similar command or routine in VBA to find the max and min values of a range of cells?

Thank you for helping me.

Excel 2000

**Marcol** · 06-06-2010, 10:20 PM

Try

Please Login or Register  to view this content.

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

**proepert** · 06-06-2010, 11:37 PM

Thank you.

How can the code ignore zero values when calculating the MIN.

Okay, so it's not really the MIN, it's the MIN value greater than zero.

**Marcol** · 06-07-2010, 05:55 AM

Try this

Please Login or Register  to view this content.

This can be used as a UDF if you want, but your Excel function runs in a macro free workbook, obviously, by definition, UDFs can't. Many companies don't allow macros.

=maxmin(E19:E40)

The macro "MaxMinusMinIgnoreZeros" calls the function in VBa

I have attached a demo workbook.

Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.