Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-15-2017, 11:47 PM
ewso ewso is offline Windows 10 Office 2016
Advanced Beginner
 
Join Date: Nov 2016
Posts: 80
ewso is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 09-17-2017, 07:26 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 574
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

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.
Reply With Quote
  #3  
Old 09-18-2017, 10:44 AM
ewso ewso is offline Windows 10 Office 2016
Advanced Beginner
 
Join Date: Nov 2016
Posts: 80
ewso is on a distinguished road
Default

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.
Reply With Quote
  #4  
Old 09-18-2017, 11:17 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 574
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

If you type
Code:
? Application.CustomList.Count
in the Immediate widow what is the count ?

please.... just reply don't quote everything.
Reply With Quote
  #5  
Old 09-19-2017, 06:04 AM
ewso ewso is offline Windows 10 Office 2016
Advanced Beginner
 
Join Date: Nov 2016
Posts: 80
ewso is on a distinguished road
Default

Quote:
Originally Posted by NoSparks View Post
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?
Reply With Quote
  #6  
Old 09-19-2017, 07:02 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 574
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

http://lmgtfy.com/?q=vba+immediate+window
Reply With Quote
  #7  
Old 09-21-2017, 02:25 AM
Debaser Debaser is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 151
Debaser is on a distinguished road
Default

Quote:
Originally Posted by ewso View Post
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.
Reply With Quote
  #8  
Old 10-10-2017, 11:59 AM
ewso ewso is offline Windows 10 Office 2016
Advanced Beginner
 
Join Date: Nov 2016
Posts: 80
ewso is on a distinguished road
Default

Debaser thanks, but can you explain how to do that?
Reply With Quote
  #9  
Old 10-11-2017, 04:37 AM
Debaser Debaser is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 151
Debaser is on a distinguished road
Default

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.
Reply With Quote
  #10  
Old 10-12-2017, 12:36 AM
ewso ewso is offline Windows 10 Office 2016
Advanced Beginner
 
Join Date: Nov 2016
Posts: 80
ewso is on a distinguished road
Default

Debaser...so I would have to type in all the items in my custom list after CustomOrder:?
Reply With Quote
  #11  
Old 10-12-2017, 01:09 AM
Debaser Debaser is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 151
Debaser is on a distinguished road
Default

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.
Reply With Quote
  #12  
Old 10-12-2017, 03:29 AM
ewso ewso is offline Windows 10 Office 2016
Advanced Beginner
 
Join Date: Nov 2016
Posts: 80
ewso is on a distinguished road
Default

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?
Reply With Quote
  #13  
Old 10-12-2017, 03:48 AM
Debaser Debaser is offline Windows 7 64bit Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Posts: 151
Debaser is on a distinguished road
Default

You could use:

Code:
CustomOrder:=Join(Application.Transpose(Range("A6:A105")), ",")
Reply With Quote
  #14  
Old 10-12-2017, 07:39 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 574
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 02:13 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft