how to get min value from a column using VBA

[johnbest]

Hi friends

I have a Column containing number. Everyday I add one value to this
column.
(say todays there are 20 rows than tomm. I will add one value and will
make 21 rows). I want to display the minimum value from these values.
How to do this using *VBA*?

Thanks in advance
JohnBest

 

[Leith Ross]

Hello John

Add a new module to your VBA project and place this code in it. You ca
then call this function from in VBA or use it as an Excel workshee
function. You only need to supply the address of the first cell. Th
macro will determine where the range ends and return the smallest valu
found

*Calling the Macro in VBA:
MinVal = GetMinValue(Range("A1")

*Calling the Macro from the Formula Bar:
=GetMinValue(A1

Code
-------------------

Public Function GetMinValue(ByRef First_Cell As Range) As Varian

Application.Volatil

Dim Col As Lon
Dim LastRow As Rang
Dim MinVa
Dim Rng As Rang
Dim Row As Lon
Dim Wks As Workshee

Col = First_Cell.Colum
Row = First_Cell.Ro
Set Wks = Worksheets(First_Cell.Parent.Name

Set LastRow = Wks.Cells(Rows.Count, Col).End(xlUp

Set Rng = Wks.Range(First_Cell.Address, LastRow.Address
MinVal = Excel.WorksheetFunction.Min(Rng

GetMinValue = MinVa

End Functio

 

[Tushar Mehta]

No need for VBA. Just create a named formula that automatically
adjusts to new data. Given your data in column A, I will assume row 1
is a header.

Then, use Insert | Name > Define... and create a name such as BaseData.
In the 'refers to' enter

=OFFSET('Basic Range'!$A$2,0,0,COUNTA('Basic Range'!$A:$A)-1,1)
where 'Basic Range' is the name of your worksheet.

Now, you can use =MIN(BaseData) wherever you want and the formula will
adjust itself as you add data to column A.

For some other applications see
Dynamic Charts
http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Dave Peterson]

If you don't have anything else in that column, you could just use:

=min(a:a)
as a worksheet formula.

In code, you could use:

msgbox application.min(activeworkbook.worksheets("sheet1").range("a:a"))