Sorting a Multi-Dimensional Array by Value, Highest to Lowest [Excel, VBA Only]

[lloydgodin]

So I have a table of values:

Col A Col B Col C
Row 1 23410 7000.00 6939.45
Row 2 23397 15000.00 11244.51
Row 3 27060 4000.00 3524.78

I need to be able to sort the values (up to 10 eventually) using an multidimensional array by Column B. The code will eventually be in a UserForm (and thus cannot rely on a spreadsheet sort).

The following code works on two conditions: 1. Only 3 rows of data (need up to 10) & 2. If it is in the order as shown (needs to be any order)

Please Login or Register  to view this content.

[LJMetzger]

Hi lloydgodin,

I have approached problems like this by converting all the numbers to strings, all having the same number of digits before and after the decimal point.

You might want to try something like the following:

Please Login or Register  to view this content.

Lewis

[lloydgodin]

Lewis,

Sorry for the late reply. I was out of the office yesterday. I took the code you used to sort and was able to get it to work. I was able to do it without converting the numbers. Here is a copy of the working code:

Please Login or Register  to view this content.

Thank you so very much for your help.

[mikerickson]

You could use something like this.
Note that getting the array from and writing it to a worksheet is just for testing purposes. The array can be gotten any way (as long as it is a 1-based two dimensional array).

The keyColumn and Descending arguments can be adjusted to your liking.
This uses a bubble sort but the "sort row numbers" approach can be used with any kind of sort.
The "build SortedArray using arrRows as a guide" explains the concept behind this.

Please Login or Register  to view this content.