Finding lowest non zero value in excel VBA

[raj]

How can i get the lowest non zero value among a set of integers within excel VBA ?

 

[s-o-s]

On 2002-05-20 09:25, raj wrote:
How can i get the lowest non zero value among a set of integers within excel VBA ?

You did not mention negative numbers assuming these are allowed the following will work.

Sub FindLowest()
IamTheLowest = 9E+50
LowAddr = ""
For Each c In Selection.Cells
    cv = c.Value
    ca = c.Address
    If cv < IamTheLowest And cv <> 0 Then
        IamTheLowest = cv
        LowAddr = ca
    End If
Next
MsgBox "Lowest Value is: " & IamTheLowest & Chr(10) & "In cell address: " & LowAddr
End Sub

If Negative numbers are not allowed.
then this will do it.

Sub FindLowest()
IamTheLowest = 9E+50
LowAddr = ""
For Each c In Selection.Cells
    cv = c.Value
    ca = c.Address
    If cv < IamTheLowest And cv > 0 Then
        IamTheLowest = cv
        LowAddr = ca
    End If
Next
MsgBox "Lowest Value is: " & IamTheLowest & Chr(10) & "In cell address: " & LowAddr
End Sub

Hope this helps.

 

[Jay Petrulis]

Another option...

Sub test()
Dim x, Rng1

Set Rng1 = Range("A1:A100")
x = WorksheetFunction.Min(Rng1)
If x = 0 Then
    x = Evaluate("=MIN(IF(" _
    & Rng1.Address & ">0," _
    & Rng1.Address & "))")
End If
MsgBox x
End Sub

Bye,
Jay

 

[purinqui]

Another option...

Sub test()
Dim x, Rng1

Set Rng1 = Range("A1:A100")
x = WorksheetFunction.Min(Rng1)
If x = 0 Then
    x = Evaluate("=MIN(IF(" _
    & Rng1.Address & ">0," _
    & Rng1.Address & "))")
End If
MsgBox x
End Sub

Bye,
Jay

Brilliant, Solution!.

 

[Rick Rothstein]

You have responded to an 18 year old thread. No matter, how about a one-liner...

Sub Test()
  MsgBox Evaluate("MIN(" & Replace(Application.Trim(Replace(" " & Join(Evaluate("TRANSPOSE(A1:A100)")) & " ", " 0 ", " ")), " ", ",") & ")")
End Sub

 

[Peter_SSs]

How about a one-liner...

or this shortening of Jay's earlier code

Sub Test3()
  MsgBox Evaluate("min(if(a1:a100<>0,a1:a100))")
End Sub

 

[Fluff]

Or even shorter

Sub Test4()
  MsgBox [min(if(a1:a100<>0,a1:a100))]
End Sub

 

[Peter_SSs]

Or even shorter

For typed characters, yes but for processes/time no.

This has a few more characters but nearly twice as fast for me.

Sub Test5()
  MsgBox = Evaluate("minifs(a1:a100,a1:a100,""<>0"")")
End Sub

 

[Rick Rothstein]

MINIFS ? It looks like I am going to have to upgrade my version of Excel (eventually)... too many new functions have been introduced since XL2010.

 

[purinqui]

Sub Test()
  MsgBox Evaluate("MIN(" & Replace(Application.Trim(Replace(" " & Join(Evaluate("TRANSPOSE(A1:A100)")) & " ", " 0 ", " ")), " ", ",") & ")")
End Sub

very good short and precise solution

You have responded to an 18 year old thread. No matter, how about a one-liner...

As old as the post is, as long as it is active and it helps users to know if a code line worked or not, it will always be useful.

MINIFS ? It looks like I am going to have to upgrade my version of Excel (eventually)... too many new functions have been introduced since XL2010.

its about time?... to update the software. Office is already with the XL2019 version.