title: Sort Macro with dynamic range and customOrder excel
body: <p>I am trying to make a multi-level sort Macro, while the amount of rows is dynamic. I've been messing around with some test data to try and get it to work, however I keep getting 1004 run-time errors from Excel. Hoping a second pair of eyes will help figure this out.</p>
  
  <p>Here is the test data</p>
  
  <pre><code>A       B        C      D
  Num Status  Junk    Junk
  1   Open    1       1
  2   Open    2       2
  3   Closed  3       3
  3   Open    3       3
  4   Open    4       4
  6   Open    6       6
  8   Open    8       8
  8   Open    8       8
  34  Open    34      34
  456 Open    456     456
  5   Closed  5       5
  853 Open    853     853
  2345Closed  2345    2345
  234 Open    234     234
  23  Closed  23      23
  4   Open    4       4
  76  Closed  76      76
  345 Open    345     345
  623 Closed  623     623
  523 Open    523     523
  4525Closed  4525    4525
  6   Open    6       6
  </code></pre>
  
  <p>And here is my Macro</p>
  
  <pre><code>Range("A1:D1").Select
  ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
  ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
      Key:=Range("B1:B10"), SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
      "Open,Closed", DataOption:=xlSortNormal
  ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add _
      Key:=Range("A1:A10"), SortOn:=xlSortOnValues, Order:=xlAscending,            DataOption:=xlSortNormal
  With ActiveWorkbook.Worksheets("Sheet1").Sort
      .Header = xlYes
      .MatchCase = False
      .Orientation = xlTopToBottom
      .SortMethod = xlPinYin
      .Apply
  End With
  </code></pre>
  
  <p>Also see the Line where i have Range("A1:A10"), want A10 to be the last row. So I was unsure what to put there.</p>
  
  <p>Thanks all,</p>
  
  <p>Jim</p>