Sorting limitations

Topic

New Reply

I know that data sorting is limited to 3 sorts in excel, but does that limitation exist in VBA. I need to sort on 5 columns, but I am not sure if that is possible?

Reply | Quote

Replies

If you want to use the built-in sort functions of XL while in VB you are limited to 3 sorts as well since they both use the same routines. If you want to create your own sorting functions you are not limited to the 3 criteria limit, but you must do all the “heavy lifting” by coding it and will be slower than the built-in routines.

Sorting by more than 3 columns can be done using the built-in features by doing multiple sort passes. First sort on the least important items then sort again on the more important items.

Steve

Reply | Quote

If you want to use the built-in sort functions of XL while in VB you are limited to 3 sorts as well since they both use the same routines. If you want to create your own sorting functions you are not limited to the 3 criteria limit, but you must do all the “heavy lifting” by coding it and will be slower than the built-in routines.

Sorting by more than 3 columns can be done using the built-in features by doing multiple sort passes. First sort on the least important items then sort again on the more important items.

Steve

Just run it as 5 individual sorts in reverse order

Meaning..

If in the sort dialogue you would do Sort1, Sort2, Sort3 from top to bottom

then in code do Sort3, Sort2, Sort1

Hence for 5 Sort sequence do

Sort5, Sort4, Sort3, Sort2, Sort1

Reply | Quote

A similar method is to use a concatanation of the Row Range similar to:

=A1 & B1 & C1 & D1 & E1

In column F1, this column can of course be hidden and will work only if it is all text.

Text conversion can be utililised such as :

=A1 & B1 & C1 & D1 & TEXT(E1,”yyyymmdd”)

Reply | Quote

A similar method is to use a concatanation of the Row Range similar to:

=A1 & B1 & C1 & D1 & E1

In column F1, this column can of course be hidden and will work only if it is all text.

Text conversion can be utililised such as :

=A1 & B1 & C1 & D1 & TEXT(E1,”yyyymmdd”)

Hi Jerry

I think there is a flaw in your logic. If for example column C consists of varying length text, column D will not be sorted properly.

Reply | Quote

Hi Jerry

I think there is a flaw in your logic. If for example column C consists of varying length text, column D will not be sorted properly.

Good point, maybe pure logic but not practical logic

Reply | Quote

Thanks all.

Reply | Quote