#1
|
|||
|
|||
delete custom lists macro
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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?
Last edited by ewso; 09-19-2017 at 06:03 AM. |
#4
|
|||
|
|||
If you type
Code:
? Application.CustomList.Count please.... just reply don't quote everything. |
#5
|
|||
|
|||
I don't know what to do with that. Where do I type it?
|
#6
|
|||
|
|||
|
#7
|
||||
|
||||
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.
|
#8
|
|||
|
|||
Debaser thanks, but can you explain how to do that?
|
#9
|
||||
|
||||
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 |
#10
|
|||
|
|||
Debaser...so I would have to type in all the items in my custom list after CustomOrder:?
|
#11
|
||||
|
||||
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.
|
#12
|
|||
|
|||
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?
|
#13
|
||||
|
||||
You could use:
Code:
CustomOrder:=Join(Application.Transpose(Range("A6:A105")), ",") |
#14
|
|||
|
|||
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 |