Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts

    hard coding the a personal list option

    I could use the person list sorting option under excel options, but I can not assume that all users of the file will have done so on their computer. Is there a way to do a pivot table sort with a personal sorting criteria for a variable called roomtemp so that is sorted by cold, warm, and hot.

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi

    You could add a custom sort order using VBA.
    If the customised sort already exists on their computer, adding it again via VBA doesn't do anything, so no harm done.
    To use any custom sort order in VBA, you need to know where about in the list of all defined custom sorts.
    Try modifying the following code to suit (for example, using a named range for your pivot and sort cell etc.):

    Code:
    Sub sortByCustomList()
    Application.addCustomList Array("cold", "warm", "hot", "very hot")
    i = Application.GetCustomListNum(Array("cold", "warm", "hot", "very hot"))
    [a10].CurrentRegion.Sort Key1:=[a10], _
        Order1:=xlAscending, _
        Header:=xlGuess, _
        OrderCustom:=i, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    End Sub
    zeddy

  3. The Following User Says Thank You to zeddy For This Useful Post:

    r3x3 (2012-03-09)

  4. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi

    Another way to sort a column containing entries like cold, warm, hot without using VBA is to simply select the data column, use Ctr-H to bring up the find-and-replace option, then replace warm with dwarm (replace all), then just use the normal alphabetic sort on your data, then after sorting, re-select and replace the dwarm entries back to warm.
    Voila! No VBA, no data gets hurt and everyone is happy.

    What I like about Excel is there are always lots of ways to achieve a result.
    And I can think of several more ways to do what you ask.
    Perhaps others may chip in their ideas too.

    zeddy

  5. The Following User Says Thank You to zeddy For This Useful Post:

    r3x3 (2012-03-09)

  6. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    For only three items you could of course just drag them into the order you want.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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