+ Reply to Thread
Results 1 to 8 of 8

Help me Simplfy and Speed Sort Routine

  1. #1
    Registered User
    Join Date
    03-03-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    53

    Help me Simplfy and Speed Sort Routine

    I am trying to tidy my code and hopefully make things a bit more efficient and faster. I have the (mostly) macro generated sort routine below which works fine but just looks very bloated and inefficient.

    Please Login or Register  to view this content.
    What I have distilled it to is that below but it fails at Key4 and I am assuming this is a throw back to pre Excel 2007 restrictions. What I need to understand is the best practice and minimum requirements for building a sort routine so I can get it straight in my head.

    Please Login or Register  to view this content.
    Thanks for any advice, all is welcome so I can finally understand what IS and what IS NOT necessary in a sort routine.

  2. #2
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Help me Simplfy and Speed Sort Routine

    Orada,

    Your first code shows that you are using Excel 2007 or newer. But, your MS-Off Ver: Excel 2003????

    I believe that Excel 2003 can only have 3 keys.

    What version of Excel are you trying to run the code in?


    Try:


    Please Login or Register  to view this content.
    Last edited by stanleydgromjr; 10-29-2012 at 02:19 PM.
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  3. #3
    Registered User
    Join Date
    03-03-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: Help me Simplfy and Speed Sort Routine

    I am using Office 2007 and of course Excel 2007.

    I tried your variation and still get Key4 highlighted with "Compile Error: Named Argument not found"

    I did believe pre Excel 2007 was limited to 3 sort keys but thought the problem was how I was writing the sort routine.

    Any ideas whay I am still getting that error, the long winded sort routine works fine, hence >=2007 ??
    Last edited by Cutter; 10-29-2012 at 09:35 PM. Reason: Removed whole post quote

  4. #4
    Registered User
    Join Date
    03-03-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: Help me Simplfy and Speed Sort Routine

    I used stanleydgromjr's variation in another single key sort, see below:

    Please Login or Register  to view this content.
    It shaved half the run time of that section of code, so big thanks to stanleydgromjr.

    Hopefully someone will help me in getting something similar and simple working with a FOUR key sort routine.

  5. #5
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Help me Simplfy and Speed Sort Routine

    Orada,

    Oops...

    Try:

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    03-03-2011
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: Help me Simplfy and Speed Sort Routine

    Firstly I should have spotted that little difference but I am still getting the "Compile Error: Named Argument not found" with Key4 highlighted.

    Just to confirm I am using Excel 2007 and the VB editor version is 6.5.1053.

    I'm afraid, I just don't know enough to understand possible reasons for this and your help is very much appreciated.

  7. #7
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Help me Simplfy and Speed Sort Routine

    Orada,

    Have been without electricity since 10/29/2012 at 10:00 PM.

    Can you attach you actual workbook?

    To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

  8. #8
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Help me Simplfy and Speed Sort Routine

    The Range.Sort method doesn't have a Key4 argument, which is why that error keeps popping up. Here's a condensed version of your original code:
    Please Login or Register  to view this content.
    That format is required if you have more than three sort keys.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1