title: Custom sorting in VBA (possibly from a list)
body: <p><strong>Table to sort:</strong></p>
  
  <p>I have a 2000-ish entry table. The first column contains an ID <strong>(non-unique)</strong> of the following type: [numeric 1-52][letters][optional underscore][optional numeric 1-10]. Letters will be either [a], [b], [c], [sa], [sb], [sc].</p>
  
  <p>Example: 1c, 10sb_3, 5a, 12c, 3sc, 21c_1, 22c_4, 22b_10, 14sb, 26sb.</p>
  
  <p><strong>How I want the sorting done</strong></p>
  
  <p>I want to sort by type (letter) first, in the order I named them before the example above. In case of same type, I want to sort by the first number. In case of same first number (both optional parameters will be present) I want to sort by the last number. The sorting should extend to the rest of the row (table) as well.</p>
  
  <p><strong>Desired end result</strong></p>
  
  <pre><code>1c
  1c
  1c
  2c
  3c
  3c
  4c_1
  4c_2
  4c_3
  5c
  6c_1
  .......
  1b
  2b
  2b
  3b
  4b_1
  4b_2
  5b
  5b
  .......
  etc
  </code></pre>
  
  <p><strong>What I intended to do</strong> (may not be the best idea)</p>
  
  <p>Using the answer of this question as a starting point: <a href="https://stackoverflow.com/questions/6100944/code-an-excel-vba-sort-with-a-custom-order-and-a-value-containing-commas">Code an Excel VBA sort with a custom order and a value containing commas</a>   </p>
  
  <p>I could make an algorithm which creates a second list, on the side, removing all duplicates, and order that list how I want manually. It would take a while, and is possibly incredibly inefficient. When it is done, I would use a piece of code similar to the answer's:</p>
  
  <pre><code>Dim oWorksheet As Worksheet
  Set oWorksheet = ActiveWorkbook.Worksheets("Sheet1")
  Dim oRangeSort As Range
  Dim oRangeKey As Range
  
  ' one range that includes all colums do sort
  Set oRangeSort = oWorksheet.Range("A1:J2000") ' &lt;&lt;&lt;&lt;I'd set the range right, of course
  ' start of column with keys to sort
  Set oRangeKey = oWorksheet.Range("B1") '&lt;&lt;&lt;What is this for?
  
  ' custom sort order
  Dim sCustomList(x To y) As String
  'There would be a loop here filling the array in order with my manually sorted list
  
  Application.AddCustomList ListArray:=sCustomList
  ' use this if you want a list on the spreadsheet to sort by
  ' Application.AddCustomList ListArray:=Range("D1:D3")
  ' ^^^^ for the record I'm not sure what this accomplishes in my case. Should I remove it? I feel it is just a different way to fill the array, using the range directly instead of filling with a loop. Maybe it suits me more?
  
  oWorksheet.Sort.SortFields.Clear
  oRangeSort.Sort Key1:=oRangeKey, Order1:=xlAscending, Header:=xlGuess, _
      OrderCustom:=Application.CustomListCount + 1, MatchCase:=False, _
      Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
  
  ' clean up
  Application.DeleteCustomList Application.CustomListCount
  Set oWorksheet = Nothing
  </code></pre>