VBA Code - Custom Sort Data based on Range

tlc53 · Aug 11, 2019

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.

lrobbo314 · Aug 11, 2019

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

tlc53 · Aug 11, 2019

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..

lrobbo314 · Aug 11, 2019

What line is throwing that error?

tlc53 · Aug 11, 2019

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

lrobbo314 · Aug 11, 2019

When the error shows up you should be able to hit debug and see one of the lines highlighted yellow.

tlc53 · Aug 11, 2019

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

lrobbo314 · Aug 12, 2019

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

tlc53 · Aug 12, 2019

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?

lrobbo314 · Aug 12, 2019

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.

VBA Code - Custom Sort Data based on Range

tlc53

Active Member

Excel Facts

lrobbo314

Well-known Member

tlc53

Active Member

lrobbo314

Well-known Member

tlc53

Active Member

lrobbo314

Well-known Member

tlc53

Active Member

lrobbo314

Well-known Member

tlc53

Active Member

lrobbo314

Well-known Member

Similar threads

Forum statistics

Share this page

VBA Code - Custom Sort Data based on Range

Active Member

Excel Facts

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock