List Custom Lists

DataPig posted about Custom Lists in Excel and his inability to delete them. I agree on all points; I’ve never needed Sun-Sat and we should be able to delete them if we want.

Here’s a macro to list all the Custom Lists to the Immediate Window.

Sub FindCustomListNumber()
   
    Dim i As Long, j As Long
    Dim sList As String
    Dim vList As Variant
   
    For i = 1 To Application.CustomListCount
        vList = Application.GetCustomListContents(i)
        sList = “”
        For j = LBound(vList) To UBound(vList)
            sList = sList & vList(j) & “,”
        Next j
        Debug.Print i, UBound(vList), sList
    Next i
   
End Sub

Apparently I’ve used custom lists in the past because there are two there that I made. Number 5 is clearly payroll item types from Quickbooks, but I don’t recall why I would put them in a Custom List. And the last one is the first 9 days of every month of the year; also a complete mystery as to why I would need that.

Posted in Uncategorized

6 thoughts on “List Custom Lists

  1. I don’t get the lack of love for the shortened weekdays, I use this list all the time.

    Lists can be very handy once you get used to them, in the past I’ve used them for listing document storage locations, pipeline references, hydraulic tool part numbers and many more. In this role however, the only other list I have is the names of our 12 teams on site. I probably use this a couple of times a week. When anyone* sees it in action it all gets a bit Arthur C Clarke (“Any sufficiently advanced technology is indistinguishable from magic”). Helps reinforce my guru status roun’ these here parts :)

    * Non Excel blog readers, that is!

  2. I use custom lists extensively, I love them. I have loads of code to add custom lists dynamically and then sort by that custom list.

    I’ve even got a list of all of the US states which I use a lot for test data.

  3. I have a shortcut (Ctrl+Shift+C) which copies selected range of cells to a custom list.

    There are some custom list which are hidden somewhere withing Excel and dont get listed and cant be deleted

    Eg
    Q1 – Drag Down gives Q2..Q4
    1st – Drag Down gives 2nd, 3rd etc

Leave a Reply

Your email address will not be published. Required fields are marked *