In the previous post (in German) I related how I was stumped for a while as to why Excel was not sorting Elements in a Pivot Tabel correctly. The reason ended up having to do with the User Defined Custom Lists.

In Examining the Problem I created this short bit of Code to look at all the User defined Custom Lists without having to go through the whole menu structure. This Macro writes every “User Defined List” in Excel in a new Worksheet.

To use the code, just insert it in a VBA Code Module. Here it is for public use:

Sub UserDefinedListReader()
Dim ListCount As Integer
Dim ListArray
Dim i As Integer, j As Integer

' Programmed: by Lukas Rohr (ExcelNova.org)
' Date: 26.11.2013
' Use: Writes all User defined Lists into a new Tab

Worksheets.Add before:=Worksheets(1)

ListCount = Application.CustomListCount
For j = 1 To ListCount
    ListArray = Application.GetCustomListContents(j)
    For i = LBound(ListArray, 1) To UBound(ListArray, 1)
        Worksheets(1).Cells(i, j).Value = ListArray(i)
    Next i
Next j
End Sub