VBA Code - Custom Sort Data based on Range

tlc53

Active Member
Joined
Jul 26, 2018
Messages
399
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.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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
 
Upvote 0
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..
 
Upvote 0
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
 
Upvote 0
When the error shows up you should be able to hit debug and see one of the lines highlighted yellow.
 
Upvote 0
Debug isn't an option that pops up.
The options that pop up with the error window are OK and Help.
 
Upvote 0
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.

<table valign="top"><caption>LEGO HTML</caption><col width="54"><col width="124"><col width="124"><col width="124"><col width="124"><col width="124"><col width="124"><col width="124"><col width="124"><col width="124"><col width="124"><col width="124">
<tr><td></td><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">A</font></th><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">B</font></th><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">C</font></th><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">D</font></th><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">E</font></th><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">F</font></th><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">G</font></th><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">H</font></th><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">I</font></th><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">J</font></th><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">K</font></th></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">43</font></th><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">500</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">44</font></th><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">200</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">45</font></th><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">600</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">46</font></th><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">400</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">47</font></th><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">100</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">48</font></th><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">300</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">49</font></th><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)"></font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">50</font></th><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">A</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">B</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">C</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">D</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">E</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">F</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">G</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">H</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">I</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">J</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">Account#</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">51</font></th><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">78</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">89</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">89</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">1</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">14</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">32</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">79</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">70</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">35</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">80</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">500</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">52</font></th><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">35</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">37</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">29</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">24</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">78</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">100</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">51</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">4</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">56</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">81</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">200</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">53</font></th><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">79</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">49</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">71</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">86</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">69</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">53</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">31</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">37</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">47</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">26</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">600</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">54</font></th><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">64</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">96</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">53</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">86</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">52</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">54</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">29</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">38</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">5</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">93</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">400</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">55</font></th><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">47</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">97</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">46</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">31</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">60</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">37</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">47</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">61</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">68</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">8</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">100</font></td></tr>
<tr><th style="background-color:rgb(166, 166, 166)"><font style="color:rgb(0, 0, 0)">56</font></th><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">68</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">31</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">14</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">33</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">90</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">57</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">40</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">55</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">17</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">74</font></td><td style="background-color:rgb(255, 255, 255)"><font style="color:rgb(0, 0, 0)">300</font></td></tr></table>
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top