delete custom lists macro

ewso · #1 09-15-2017, 11:47 PM

I often use custom lists in my sorting. These custom lists change a lot and old lists accumulate. Is there a macro I can use that can quickly delete all custom lists I have so I don't have to manually delete all of them?

thanks

NoSparks · #2 09-17-2017, 07:26 AM

From https://stackoverflow.com/questions/...g-lists-in-vba

Code:

Sub RemoveCustomLists()
    Dim i As Long
    On Error Resume Next
    For i = 1 To Application.CustomListCount
        Application.DeleteCustomList (i)
    Next i
    On Error GoTo 0
End Sub

This may also be of interest.

ewso · #3 09-18-2017, 10:44 AM

Nosparks thanks, but for some reason that macro doesn't work. Before I posted this question, I had also tried another macro that I found from another site, but it didn't work either. When I run the macro and check if it deleted the custom lists, it looks like it does at first, but then when I check again later, the custom lists are there again. Do you know what the problem could be?

NoSparks · #4 09-18-2017, 11:17 AM

If you type

Code:

? Application.CustomList.Count

in the Immediate widow what is the count ?

please.... just reply don't quote everything.

ewso · #5 09-19-2017, 06:04 AM

Quote:

Originally Posted by NoSparks

If you type

Code:

? Application.CustomList.Count

in the Immediate widow what is the count ?

I don't know what to do with that. Where do I type it?

NoSparks · #6 09-19-2017, 07:02 AM

http://lmgtfy.com/?q=vba+immediate+window

Debaser · #7 09-21-2017, 02:25 AM

Quote:

Originally Posted by ewso

it looks like it does at first, but then when I check again later, the custom lists are there again.

That sounds as if your code is recreating them. In versions post 2003, you don't actually need to add a custom list to sort by one - you can specify the list directly in the properties of the Sort.

ewso · #8 10-10-2017, 11:59 AM

Debaser thanks, but can you explain how to do that?

Debaser · #9 10-11-2017, 04:37 AM

For example:

Code:

    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Worksheets("Sheet1")
    With ws.Sort
        With .SortFields
            .Clear
            .Add Key:=ws.Range("A2:A19"), SortOn:=xlSortOnValues, _
                    Order:=xlAscending, CustomOrder:="h,k,a,d,e", _
                    DataOption:=xlSortNormal
        End With
        .SetRange ws.Range("A1:A19")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Just amend the CustomOrder string argument.

ewso · #10 10-12-2017, 12:36 AM

Debaser...so I would have to type in all the items in my custom list after CustomOrder:?

Debaser · #11 10-12-2017, 01:09 AM

Not necessarily. You can use any function that returns a delimited string like that - e.g. if you already had those items in some cells.

ewso · #12 10-12-2017, 03:29 AM

so if I had 100 items in cells A6-A105 to use as a custom list, what would I need to do to make it work?

Debaser · #13 10-12-2017, 03:48 AM

You could use:

Code:

CustomOrder:=Join(Application.Transpose(Range("A6:A105")), ",")

NoSparks · #14 10-12-2017, 07:39 AM

Not sure if this thread is any longer about deleting custom lists or not,
but it would be interesting to know the answer to post #4

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Newbie - Need help with a macro and drop down lists	vipvanilla	Word VBA	4	02-13-2014 01:30 PM
Macro to add periods into MAC address lists	auriuman78	Word VBA	1	01-29-2014 10:30 AM
Cannot delete old 2003 custom menu from 2010 template	Smallweed	Word	1	10-02-2013 03:53 PM
custom icon, undo/redo for macro, permanent macro	Rapier	Excel	0	08-05-2013 06:30 AM
Delete custom heading styles	ubns	Word	1	07-31-2012 08:35 PM