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
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.
Ahh, but there’s so many other things we should be able to do in excel.
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!
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.
I haven’t used them in a while, but I believe they were useful in providing a way to sort in non-A-Z or Z-A order without having to prepend a number.
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
Hi Sam,
Those are cool – how did you find them??
Alan.