+ Reply to Thread
Results 1 to 9 of 9

Automatically sort and re-order?

  1. #1
    Registered User
    Join Date
    05-08-2008
    Location
    NH
    Posts
    10

    Automatically sort and re-order?

    Hi there,

    I have a spreadsheet where column c is for a Priority. We want to put a number in there to represent the priority order that this item will be worked on. We want to number them such as 1, 2, 3, 4....20. However, if I want to change the number of one of these items, I would like it to automatically re-sort the rows according to this new number. I also do not want any duplicates, so I am unsure how to do this.

    Any help would be awesome!!

    Carrie
    Last edited by VBA Noob; 05-26-2008 at 02:07 PM.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi,

    You could use conditional formatting to identify any duplicates. Assuming your data including column labels is in A1:C21, put the following conditional format, (using the Formula Is option), in C2 and set it to say a Red pattern
    Please Login or Register  to view this content.
    Now to get columns A:C to sort whenever you make a change in col. C add the following code to the Sheet Selection_Change event in the VB Environment.

    Please Login or Register  to view this content.

  3. #3
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,230
    This auto-sort routine notes any duplicates and asks the user to resolve them at the time of entry. It maintains column C to be 1,2,3,4,...,n.

    in the Sheet's code module
    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464
    Hi,

    Just out of interest, what's the VBA instruction "Rem sort range" and "Rem re-number 1 to n"? I've not come across this before.

    Is 'Rem' a VBA keyword new to Excel 2007, and what's the syntax? I note there are spaces in the characters following the Rem.

    Rgds

  5. #5
    Registered User
    Join Date
    05-08-2008
    Location
    NH
    Posts
    10
    Hi and thank you both for the responses, however I could not get either to work.

    I'm attaching my xls sheet if someone could maybe take a look?

    Its Column C that I want to sort by what number is in there.

    Thanks!
    Attached Files Attached Files

  6. #6
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,230
    Quote Originally Posted by Richard Buttrey
    Hi,

    Just out of interest, what's the VBA instruction "Rem sort range" and "Rem re-number 1 to n"? I've not come across this before.

    Is 'Rem' a VBA keyword new to Excel 2007, and what's the syntax? I note there are spaces in the characters following the Rem.

    Rgds
    Rem is an old school indicator for a comment.
    I understand that folks with better eyes than mine can see the apostrophy that is being introduced as an alternate syntax for comments.

  7. #7
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,230
    I'm having trouble downloading that file, it gets to 67.7 KB and stalls.

  8. #8
    Registered User
    Join Date
    05-08-2008
    Location
    NH
    Posts
    10
    Hi - I'm attaching it as a zip. Maybe that will work better.

    Thanks!
    Carrie
    Attached Files Attached Files

  9. #9
    Forum Guru mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,230
    Yes, attaching zipped files is probably best.

    I've adjusted the code to your data lay-out.
    To move the routine to the Simulations page, all that needs to be done is exchange this line at the start of the routine.
    Please Login or Register  to view this content.
    This is a pretty volitile way to move data around, so I included an undo. Type "UNDO" into any cell in the priority column and the last re-ordering will be un-done.
    Attached Files Attached Files

+ 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