Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by sdckapr View Post
    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
    Andrew

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    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")
    Jerry

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Jezza View Post
    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.
    Regards
    Don

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Quote Originally Posted by wdwells View Post
    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
    Jerry

  7. #7

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •