Can I use MIN with a 2 Dim Array?

[dlowrey]

Hello,
I have created and populated a 2-dimension array.
I think of arrays as rows and columns. aDistance(1,1) is a Name, aDistance(1,2) is a distance
I don't know how to reference all of the values in the second column of the array.

Dim aDistance() As Varient
ReDim aDistance(Last_Row, 2) 'Last_Row is variable of a range of rows in a worksheet, code not included here for clarity
The array is fully populated dynamically by other vba code. The resulting sample data set has 34 rows.

I want to obtain the minimum distance from all of the values in the Distance column of the array.
I am hoping the below will work, IF I knew how to reference the range of values in the second column.
mClosestPt = Min(aDistance(something here?))

Thank you for your time.
-DL

 

[6StringJazzer]

Give this a try:

mClosestPt = Application.WorksheetFunction.Min(Application.WorksheetFunction.Index(aDistance, 0, 2))

 

[lrobbo314]

Here is an example. The result from running the code returns 3.

Book1.xlsx
	I	J
5	A	3
6	B	40
7	C	500
Report 2

Sub MINTD()
Dim AR() As Variant:    AR = Selection.Value2

With Application
    Debug.Print .WorksheetFunction.Min(.Index(AR, 0, 2))
End With
End Sub

 

[Eric W]

The above 2 posts show how to use Index to reference a single row or column of an array. But if your first column is all text values, you can apply the Min function to the whole array:

Debug.Print WorksheetFunction.Min(aDistance)

since Min ignores non-numeric values.

 

[dlowrey]

Wowie Eric W!
That is fantastic. I spent hours trying to figure this out and you did it beautifully.
Thank you.
-DL

 

[Eric W]

Glad we could help!

Keep the Index idea in mind though, sometimes you might have an array with multiple numeric columns.

 

[6StringJazzer]

The above 2 posts show how to use Index to reference a single row or column of an array. But if your first column is all text values, you can apply the Min function to the whole array:

I got so focused on the question that I forgot to solve the problem