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: 72
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
Posts: 388
NoSparks is on a distinguished road
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: 72
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
Posts: 388
NoSparks is on a distinguished road
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: 72
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
Posts: 388
NoSparks is on a distinguished road
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: 136
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: 72
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: 136
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: 72
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: 136
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: 72
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: 136
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
Posts: 388
NoSparks is on a distinguished road
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
Please reply to this thread with any new information or opinions.

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 03:02 AM.


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