Need VBA code to sort an array

TotallyConfused · Nov 17, 2018

Hello all you MrExcel experts

I have used the following code to load the data into an array which I’ve called ‘ARY’. The data can vary in number of rows and possibly the number of columns. Because I’ll be searching and updating the data several times, I thought loading it into an array would speed up the process, then when I’m finished, the array will be written back to the sheet. I need to be able to sort the array in ascending order and using the data in column ‘A’ as my key. Of course, I need to have column ‘B’ (and any other columns) rearranged also. The data does have headings.

1 ) I have found VBA code for sorting from the sheet but haven’t tried them yet, but is there a way for VBA to sort the data after it’s loaded into an array, which would be faster, considering it will need to be done several times?
2 ) Would the same code work if the array needed to be sorted using, for example, data from the second column as the key?

I know the array is created and loaded properly, because I’ve used MsgBox to display different combinations of array elements. My code for loading that array is as follows:

Code:

Option Base 1

  Sub LoadArrayColumns()

      Dim ARY as Variant
      ARY = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
  End Sub
   
  My data sample:
           A                 B
  1       LENGTH       QUANTITY
  2          66                  6
  3          55                  5
  4          11                  1
  5          77                  8
  6          22                  2
  7        66.5                  7
  8          44                  4
  9          33                  3

Thank you for any help or advice you may be able to offer.

TotallyConfused

Fluff · Nov 17, 2018

Have a look here http://www.cpearson.com/excel/SortingArrays.aspx

TotallyConfused · Nov 20, 2018

Fluff said:
Have a look here http://www.cpearson.com/excel/SortingArrays.aspx

Hello Fluff

I owe you an apology for not getting back to you sooner. Things have been a bit busy around here the past few days. THANK YOU for sending me that link to CPearson.Com for information about sorting data after its loaded into an array. That is an amazing site, and I think I could spend a month there and still not have read everything. I’ve made a note of the site as I’m sure it will come in handy in the future.

Thanks again for your help and for all the work you do to help keep this site flowing as smoothly as it does. You and the other Moderators do a terrific job here.

TotallyConfused

Fluff · Nov 21, 2018

You're welcome & thanks for the feedback

Need VBA code to sort an array

TotallyConfused

Board Regular

Excel Facts

Fluff

MrExcel MVP, Moderator

TotallyConfused

Board Regular

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Need VBA code to sort an array

TotallyConfused

Board Regular

Excel Facts

Fluff

MrExcel MVP, Moderator

TotallyConfused

Board Regular

Fluff

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock