Getting the maximum value of a specific column in a 2d array

Asked Sep 21 '20 at 20:15

Active Sep 21 '20 at 20:44

Viewed 33 times

In continuation of my previous question I now need the maximum value contained within one column of the array.

I have a 2d array, with flexible dimensions:

arr_emissions(0 to n, 0 to m)

Where n is 22 or larger (but no larger then around 400), and m is 6 or larger.

In the smallest case column m = 6 contains the sum of columns m = 2 - 5. I now need the maximum value of this column.

For 1d arrays there are plenty of examples of the usage of application.worksheetfunction.max. So I thought to combine that with application.worksheetfunction.index() as I did last time, but in this case the row number is a required argument, whilst I want the maximum value within a column, so from every row.

I tried wks_AI_res.Cells(row, 11).Value = Application.Max(Application.Index(arr_emissions, 0, emissions_rows)) which yields me the #VALUE error. In this the value of emissions_rows = 6.

As the dimensions of the array are flexible I would like to implement a robust method, that preferly doesn't loop over the entire array.

edited Sep 21 '20 at 20:44

asked Sep 21 '20 at 20:15

Luuklag

3,897
11
38
57

the row is required but `0` will return the whole column. – Scott Craner Sep 21 '20 at 20:17
@ScottCraner thanks for the tip. In my case the array is 0-based, and I get the #Value error returned. – Luuklag Sep 21 '20 at 20:22
Then create a function that will return a 1D array of the given column of a provided 2d array. – Scott Craner Sep 21 '20 at 20:25
@ScottCraner solved it by creating a 1D array that contains the row numbers of 1 till max, and enter that as row requirement in the `MAX()` function – Luuklag Sep 21 '20 at 21:20

Getting the maximum value of a specific column in a 2d array

0 Answers0