Minimum value ignoring zero

[Guest]

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

 

[Bob Phillips]

Hi Caroline,

Try

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

it cannot be whole columns

--

HTH

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

 

[Dave Peterson]

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

 

[Guest]

thanks to both of you.
--
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