Sorting Macro

Hi

Right now I'm using the excel built in function to sort columns with my macro
However since my columns have 64,000 entries it takes a while for the computer to sort it.
Is it possible to make it faster? Like using a Heap or Merge Sort?
I am already reading the columns with an array.
Can someone show me how to do this sort if possible?

Thanks

Use this line of code at the beginning of ANY macro to speed it up, it makes a massive difference:

Please Login or Register  to view this content.

That helps a bit, but it's still slow.
Any other ideas?

Not that I know of sorry

I think the Array solution will work, just dont know how to do write it.
If anyone can help?

So I put the name of my array in LT?
I have an array that works like this.
MyArray(r,1)
r being the column number

Thanks

You put the range you want sorted into sortThisRange. It will sort that range and put it back on the sheet depending on the [optional] offset.

If you have a one dimensional array, sortRoutine will take care of it. But LT will have to be modified.
As set, it checks the order of the first column of the range.

I'll be off work in 8 hours and will be able to devote more time to fitting this routine to your needs.

Originally Posted by Dohko

So I put the name of my array in LT?
I have an array that works like this.
MyArray(r,1)
r being the column number

Thanks

Are you using the code

Please Login or Register  to view this content.

and getting myArray; a 2 dimensional array with, LBound(myArray,2)=UBound(myArray,2)=1?


If so, you may prefer

Please Login or Register  to view this content.

which gives you a one dimensional array where UBound(myOtherArray)=UBound(myArray,1).

This routine will sort a one dimensional array.
Call orderRoutineOneDim(yourArray, [decendingSort]) will sort a one dimensional array. If variable decendingSort is False (default) it will be sorted ascending. If True, decending.

Please Login or Register  to view this content.

This routine will sort an array dimensioned such that the first index varies, but the second index is always 1 (Dim myARRAY (Low to High, 1 to 1))
orderRoutineTwoDim accepts the same kinds of variables as orderRoutineOneDim.

Please Login or Register  to view this content.

Both these routines sorted arrays of 10,000 elements in 1 second, 50,000 in 4 sec, and 100,000 in 7 sec.

As is, they sort on "<". If you want a differnet kind of sort (like on the value of strings), you'll have to use the custom LT function. If you are sorting a squarer array, a quick modification of the Range sort in the previous post would probably be best.

These are rather generalized routines. If I had a better picture of what values you are sorting and to what end, we tighten up the code and get the excicution time down.

Thanks for the code.
But I still need some help.

Please Login or Register  to view this content.

Here's how my Array is set up.
Will it be possible to use your function and sort everything at the end of my code?
Sorry if its a bit of a mess

Thanks

Call sortRoutineOneDim(cArray) should do it.

As to your other question

Please Login or Register  to view this content.

will sort columns A and B.

Thanks!

So If I have
A B C
1 2 5
2 3 3
3 9 8
4 1 9

Will this sort everything? like if I sort Column B i will move all numbers across al columns?
The I need to sort back to Column A at the end of my macro.

sortThisRange will sort only those cells in the range that is input.

As posted above, it will sort on the first column of that range. If you want it to sort differently, then LT will need to be modified.

if you want columns A,B, and C sorted,

Please Login or Register  to view this content.

Will set the range.


Using the sortThisRange routine, if you want the equivilant of Sort first by column A and then by B (ascending both)

Use this

Please Login or Register  to view this content.

Sure. (Look out for typos, I was up till 3AM settiing up stages)
This loop will do it. You'll need to specify "mySheet" and change the range of values that colNum varies through.

Please Login or Register  to view this content.

The problem is that you said that you have 60,000 entries in each column. Times 200 rows that is huge. I doubt that my computer (Mac.G4)could hold a single sheet with that much on it. Excel is probably not the right program for your need.

The size also brings a time issue into this, which brought you to this forum.

SortThisRange will read a 10,000 row column from a spreadsheet, sort it and write it back to the spreadsheet in 3'ish seconds. It will do the same for a 20,000 row column in 33'ish seconds. My guess is that that is pushing the memory limit and garbage collection is occuring more often and slowing things down.

Since it doesn't read from or write to the spread sheet, sortRoutine is much faster than SortThisRange.

I think if that's the case, then I'll just use excels sort function.
It takes about 10 seconds per column.

Thanks a lot for your help.