Sort a table using custom list from another table

[dhregan]

Hello experts -

I have two tables - both are dynamically ranged - as follows:

1. TABS_INVENTORY spanning columns Q:R
2. RANGE_INVENTORY spanning columns J:O

The values in column Q (also a dynamic range called "TABS_ONLY") are the "non-duplicated" values from column J. In column R (another dynamic range called "TABS_ORDER"), the user can specify the "sort order" for the RANGE_INVENTORY table based on numeric values in column R. From VBA, I have been able to construct a custom sort order / list, but when I try to apply it to the RANGE_INVENTORY table, the custom sort order is not reflected.

Here is the VBA:



Dim Sort_List1 As Long Dim Sort_List2

Range("TABS_INVENTORY").Select
ActiveWorkbook.Worksheets("Values").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Values").Sort.SortFields.Add Key:=Range("TABS_ORDER"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Values").Sort
.SetRange Range("TABS_INVENTORY")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

ActiveWorkbook.Worksheets("Values").Sort.SortFields.Clear
Sort_List2 = Join(Application.Transpose(Range("TABS_ONLY").Value), ", ")

Range("RANGE_INVENTORY").Select
ActiveWorkbook.Worksheets("Values").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Values").Sort.SortFields.Add Key:=Range("ROWS_ONLY"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Values").Sort
.SetRange Range("RANGE_INVENTORY")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

ActiveWorkbook.Worksheets("Values").Sort.SortFields.Clear
Application.AddCustomList ListArray:=Array(Sort_List2)

' I have tried the line below by replacing the 5 with a 1, but no luck. I found that with +5, I am able to delete the custom list after the sort is done.

Sort_List1 = Application.GetCustomListNum(Range("TABS_ONLY").Value) + 5
ActiveWorkbook.Worksheets("Values").Sort.SortFields.Add Key:=Range("WORKSHEETS_ONLY"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
Sort_List1, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Values").Sort
.SetRange Range("RANGE_INVENTORY")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
On Error Resume Next
Application.DeleteCustomList (Sort_List1)
On Error GoTo 0


The range WORKSHEETS_ONLY is a dynamic range representing column J from the overall RANGE_INVENTORY range. I suspect the issue is with the lines immediately following my inline comment above, but I cannot get it to work. Any guidance would be greatly appreciated.

[mdmackillop]

Please post a sample workbook; Go Advanced/Manage Attachments

[dhregan]

Hello - Workbook is attached.

[Aflatoon]

Instead of adding custom lists, you can simply specify the order directly:


ActiveWorkbook.Worksheets("Values").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Values").Sort.SortFields.Add Key:=Range("WORKSHEETS_ONLY"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:=CStr(Sort_List2), DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Values").Sort
.SetRange Range("RANGE_INVENTORY")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

[Paul_Hossler]

Instead of adding custom lists, you can simply specify the order directly:

That's a handy tip -- I didn't know you can do that.

I've been putting things like "1-Critical" and "2-Warning" and "3-Info" into sheets just to sort them without messing with custom lists

[dhregan]

Hello Aflatoon -

This worked perfectly. Thank you so much.