Wsrtjtyk

I'm trying to find the minimum non-zero value. I found the following example on the web and tried to adapt it to VBA with no avail.

=SMALL((A1,C1,E1),INDEX(FREQUENCY((A1,C1,E1),0),1)+1)

Here is my adaption:

myarr = Array(A, B, C)

.Range(Cells(D, 1), Cells(WorksheetFunction.Small(myarr,WorksheetFunction.Index(WorksheetFunction.Frequency(myarr, 0), 1) + 1),1)).EntireRow.Delete

where A, B, C, D are four integers.

In the debug window, the SMALL function returned "out of context" and as soon as this line of code was executed, the compiler exit the sub as if an "Exit Sub" command was executed.

My guess is FREQUENCY function is not working. How to make it work?

For only three values, it's easy to use a non-function solution such as nested IFs but I'm interested in how to make the functions work as I might need it in the future.

Not entirely sure what you are doing but you can do MIN IF inside of Evaluate with an array

Option Explicit

Public Sub GetMinGreaterThanZero()

    Dim arr()

    arr = Array(0, 4, 2, 1)

    Debug.Print Evaluate("=MIN(IF({" & Join(arr, ";") & "}>0,{" & Join(arr, ";") & "}))")

End Sub

There are several options here - the simplest option, if you have Office 365, is to use the MINIFS function, like so: WorksheetFunction.MinIf(myarr, myarr, "<>0")

But, since you haven't tagged the question as office365, I will use a different option:

Private Function MinPositive(Values As Variant) As Variant

    MinPositive = CVErr(xlErrValue)

    On Error GoTo FuncErr

    Dim TempVal As Double, Pointer As Long

    If IsArray(Values) Then

        TempVal = WorksheetFunction.Max(Values) 'High bound

        If TempVal <= 0 Then Exit Function 'No values greater than 0

        For Pointer = LBound(Values) To UBound(Values)

            If CDbl(Values(Pointer)) > 0 And CDbl(Values(Pointer)) < TempVal Then

                TempVal = CDbl(Values(Pointer)) 'Swap for lower number

            End If

        Next Pointer

    End If

    MinPositive = TempVal 'Output the Min value greater than 0

FuncErr:

End Function