How can I found the minimum value in a list ignoring the zeros?
for the moment I only know
MinValue = Application.WorksheetFunction.Min(Range("A:A"))
thanks in advance
--
caroline

Hi Caroline,

Try

Activesheet.evaluate("=MIN(IF(ABS(G1:G20)<0,G1:G2 0))")

it cannot be whole columns

--

HTH

RP
(remove nothere from the email address if mailing direct)


"caroline" wrote in message
...
How can I found the minimum value in a list ignoring the zeros?
for the moment I only know
MinValue = Application.WorksheetFunction.Min(Range("A:A"))
thanks in advance
--
caroline

In a worksheet cell, I'd use an array formula like:

=MIN(IF(A1:A65535<0,A1:A65535))
(hit ctrl-shift-enter instead of just enter)

But notice that this array formula can't use the whole column.

In code, I'd use something like:

Option Explicit
Sub testme()

Dim minVal As Variant
Dim myRng As Range

With Worksheets("sheet1")
Set myRng = Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

minVal = Application.Evaluate("min(if(" _
& myRng.Address(external:=True) & "<0," _
& myRng.Address(external:=True) & "))")

MsgBox minVal
End Sub

caroline wrote:

How can I found the minimum value in a list ignoring the zeros?
for the moment I only know
MinValue = Application.WorksheetFunction.Min(Range("A:A"))
thanks in advance
--
caroline

--

Dave Peterson

thanks to both of you.
--
caroline


"Dave Peterson" wrote:

In a worksheet cell, I'd use an array formula like:

=MIN(IF(A1:A65535<0,A1:A65535))
(hit ctrl-shift-enter instead of just enter)

But notice that this array formula can't use the whole column.

In code, I'd use something like:

Option Explicit
Sub testme()

Dim minVal As Variant
Dim myRng As Range

With Worksheets("sheet1")
Set myRng = Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

minVal = Application.Evaluate("min(if(" _
& myRng.Address(external:=True) & "<0," _
& myRng.Address(external:=True) & "))")

MsgBox minVal
End Sub

caroline wrote:

How can I found the minimum value in a list ignoring the zeros?
for the moment I only know
MinValue = Application.WorksheetFunction.Min(Range("A:A"))
thanks in advance
--
caroline

--

Dave Peterson