dhregan
06-28-2017, 05:37 PM
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.
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.