Sort a 2 dimension array

Hi folks,

I've created a public array

Please Login or Register  to view this content.

This encompasses 2 header rows.

blank, blank, blank, date, blank, etc...
Record# Name owner data1 data2 etc
3 Smith trust xyz abc etc
4 Jackson joint jkl efg etc
.
.
.
250

I need to sort this into a new array based on alphabetizing column 2 (Name) such that I can populate a multi-column listbox and then when a Name is selected from the list box, I can execute some code on the record involved.

Thanks

Hi Tradinup2
you can sort the data on a sheet previously
for example

Please Login or Register  to view this content.

That sorts the sheet (header rows, user form buttons and all) but doesn't seem to create an array, so I tried this

Please Login or Register  to view this content.

Same result - sorts the sheet.

It works for me

Please Login or Register  to view this content.

, Order3:=xlAscending is superfluous

Sheet1 contains the data that the array "MyArray()" is created from. I don't want to sort that sheet, I need to create a new array "MyNewArray()" that contains the data from "MyArray" sorted by alphabetizing the second column "Name" on sheet1.

Okay, then try to use the Shell sort

Please Login or Register  to view this content.

Thanks nilem,
I have to take my wife to the Dr. and will be gone for a couple of hours, but I'll try that as soon as I get back.

Nilem, That seems to work and sort quickly. Is there a way to create or re-dimension the array to allow for the 2 header rows?

Also how can I put the array onto a temporary sheet named "Temp"?

Thanks for the help.

This is based on "QuickSort" algorithm.
It will sort 2D array between specified row indexes.

VSortM Variable(array), StartRowIndex, LastRowIndex, RefColumn, Order(1 for Ascending, 0 for Descending)

Please Login or Register  to view this content.

Well, I spoke too soon as that still seems to sort the sheet that provides the data for the array.

Jindon -

Please Login or Register  to view this content.

The array is filled completely at the first stop. However, after the sort routine (second stop) the only part of the array that's left is the top two rows, so it seems like we've got the startrow and lastrow index reversed and I'm not smart enough to fix it.

any further help appreciated.

I thought you have header on 1st and 2nd row....

If you don't have header, it should be 1 (from the top).

Please Login or Register  to view this content.

Is this what you wanted?

Yes I do have 2 header rows, but after the sort the array and sheet14 only have the 2 header rows, so I thought maybe the routine is reversing the upper and lower bounds.

we seem to be getting the exact opposite of what we want.

thanks

I don't understand what you mean by opposite...

Do you want to sort Descending?
If so, the last arg should be 0

Please Login or Register  to view this content.

Otherwise I need to see your file.

I'll have to send the file tomorrow, but the problem is:
we want sort the array ascending, but after the sort instead of getting rid of the 2 header rows, the only thing we have left is the 2 header rows.

Did you see far down?

Sort the array moves blanks at the top when sort ascending.

It that a problem you've got?

yes that's the problem and we need to sort on the second column. Is there a way to re-dimension the array to check for the last row in colA and sort colB?

thanks for your patience.

Then reference column should be 2

Please Login or Register  to view this content.

If you don't have blanks in 2nd column, it should sort properly.

Sorry, I should have picked up on the column variable.

The blanks appear in the unfilled rows at the bottom of the range because I set the range in anticipation of the finished product. The array will always be dynamic however. Can I use this:

Please Login or Register  to view this content.

before the sort call and get rid of the unused rows at the bottom?

For the sorted array to be available in all user forms and subs, do I need to declare it as Public MyArray as variant in module 1?

I really need to see your sheet layouts, but just try

Please Login or Register  to view this content.

I'll try that in the morning and send the sheets.

thanks again.

Please Login or Register  to view this content.

This did the trick.

Thanks for your help & patience.