Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Thread: VBA Code - Custom Sort Data based on Range

  1. #1
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    305
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Code - Custom Sort Data based on Range

    Hi,

    I have data headings on cells A50:K50. The length of the data underneath can not be determined, as it'll change all the time.

    I would like to sort the data underneath by account number, which is located in column K (header K50). The order I would like it sorted by is listed under K43:K48.

    I've had a look but can't find information on a VBA code to sort like this.

    Can anyone help with this please?

    Thank you.

  2. #2
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Code - Custom Sort Data based on Range

    Try this. You say that it is sorting account numbers. You have to have the account numbers and the custom list numbers be stored as text because Excel custom lists only accept text not numbers.

    Code:
    Sub CustomSort()
    Dim r As Range
    Dim cust As Range
    
    Set r = Range("A50:K" & Range("A" & Rows.Count).End(xlUp).Row)
    Set cust = Range("K43:K48")
    
    Application.AddCustomList cust
    r.Sort key1:=[K50], order1:=1, ordercustom:=Application.CustomListCount + 1, Header:=xlYes
    Application.DeleteCustomList Application.CustomListCount
    End Sub
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  3. #3
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    305
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code - Custom Sort Data based on Range

    Thanks! I ensured my custom list and account number format list was stored as "text" and I inserted and tried running your code.
    It returned VBA error 400..

  4. #4
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Code - Custom Sort Data based on Range

    What line is throwing that error?
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  5. #5
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    305
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code - Custom Sort Data based on Range

    When I run the code under VBA it doesn't define a line, it comes back with this;
    Run-time error 1004 - Application-defined or Object-defined error

  6. #6
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Code - Custom Sort Data based on Range

    When the error shows up you should be able to hit debug and see one of the lines highlighted yellow.
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  7. #7
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    305
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code - Custom Sort Data based on Range

    Debug isn't an option that pops up.
    The options that pop up with the error window are OK and Help.

  8. #8
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Code - Custom Sort Data based on Range

    I'm not sure what could be causing that error to happen. I threw together some sample data that I used the code on and it worked. I am pasting it below. It has random numbers in columns A:J, and simple account numbers in column K. Maybe you can see some difference between my test data and yours to see what might be happening. Also, for the account numbers, I added a ' before each one to force them to be text, so '500, '600, etc. Maybe changing that would fix the issue.
















    LEGO HTML
    ABCDEFGHIJK
    43500
    44200
    45600
    46400
    47100
    48300
    49
    50ABCDEFGHIJAccount#
    517889891143279703580500
    5235372924781005145681200
    5379497186695331374726600
    546496538652542938593400
    554797463160374761688100
    5668311433905740551774300
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  9. #9
    Board Regular
    Join Date
    Jul 2018
    Location
    Auckland
    Posts
    305
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code - Custom Sort Data based on Range

    Thanks. You're right, using your test data the code does work. Your test data even works when only the custom list is "text" and the account number data is "numbers" or "general".
    I just can't work out why it's not liking my data. I tried using your test data but just changing the account numbers to my actual account numbers.
    They are;
    36123
    36124
    36125
    36422
    36122
    36186
    I found using these account numbers, the code stops working and it spits out the same error again.
    Would you mind seeing if you have the same outcome using these account numbers?

  10. #10
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Code - Custom Sort Data based on Range

    I don't know what the issue is. I used your account numbers and it still worked fine. Even like you said, with numbers in the table and text as the custom sort range, it still works. If I invert them, that is to say, numbers in the custom sort range, then I do get an error, but it's different than the one you mentioned. This error says, 'Method AddCustomList of object failed'. I haven't been able to reproduce the error you're receiving.
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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