Let's Talk Excel

Create a new Custom List from selected Cells

Custom lists are very useful in Excel because they are a quick way to get multiple entries into cells and sort pivot tables in the desired manner. They are constantly used to get a sequence of month names or names of days such as Monday, Tuesday….

You can see which Custom Lists are already installed by going to

FILE/Options and then Advanced, and scroll almost to the bottom and then choose Edit Custom Lists….

Create a new Custom List from selected Cells

At this point you will see the Custom Lists that are available to you.

Create a new Custom List from selected Cells

In the same window you can add your own custom list by entering the list manually divided by semicolon or by selecting cells containing the new list and pressing Import.

But this can be quite time consuming and can be achieved faster by the following VBA code

Sub XET_CustomLists_CreateFromCells()
    Dim rows As Long, cols As Long
    Dim sheetlist(1024)
    shc = 0
    
    rows = Selection.rows.Count
    cols = Selection.Columns.Count
    If (rows > 0) And (cols > 0) Then
        For Each c In Selection
            If IsNumeric(c.Value) Then
                MsgBox "Numbers not valid Custom List values"
                Exit Sub
            End If
            If c.Value = "" Then
                MsgBox "Null values not valid Custom list values"
                Exit Sub
            End If
        Next
        Application.AddCustomList Selection
        
    Else
        MsgBox "No selection, no fun"
    End If
End Sub

This Code can also be found in an Xnet Excel Tools AddIn for Excel which is free and can be downloaded here.

Leave a Reply

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

%d bloggers like this: