My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

Listing and Deleting Custom Styles

You are here: Home / Excel VBA / Listing and Deleting Custom Styles
listing and deleting custom styles
by

If you end up with a workbook that contains custom styles, removing all of these may be a pain depending on how many there are.

With a little VBA we can easily list and delete all custom styles.

In my workbook I have three custom styles, Cool Style 1, Gaudy Style and Sunglasses Required.

Custom styles in Ribbon

The first routine I've written is called List_All_Styles

Download the Example Workbook

All of the VBA code I use in this post is in the example workbook. Download it and use it yourself.

Enter your email address below to download the workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

List styles in workbook VBA

By running this we get, not surprisingly, a list of styles in the workbook, and custom created styles are marked as such.

List of custom styles in workbook

If there are only a handful of custom styles you can delete them by hand, but if there are lots just run Delete_Styles to get rid of them.

Delete Styles VBA

A Note on Looping in VBA

You may have noticed that the For loop that deletes the styles is counting down, rather than up, which you might normally expect.

This is because when the For loop is started the value of Styles.count is set to however many styles there are.

Let's say it is set to 5, so our loop will execute 5 times, from 1 to 5.

If during this loop we delete a style, the number of styles in the workbook decreases to 4. But we've told our loop to execute 5 times.

When it tries to execute for the 5th time the .Styles(counter) reference is invalid because counter is 5 but there isn't a 5th style in the workbook now because we deleted one. VBA will generate an error here.

In scenarios like this you need to start the For loop counting down from the number of styles in the workbook (.Styles.count) to 1.

That way, if your code deletes a style, .Styles(counter) won't try accessing a style that does not exist.

listing and deleting custom styles

Microsoft Power BI Community Super User Logo

AUTHOR Philip Treacy Co-Founder / Owner at My Online Training Hub

Systems Engineer with 30+ years working for companies like Credit Suisse and E.D.S. in roles as varied as Network & Server Support, Team Leader and Consultant Project Manager.

These days Philip does a lot of programming in VBA for Excel, as well as PHP, JavaScript and HTML/CSS for web development.

He's particularly keen on Power Query where he writes a lot of M code.

When not writing blog posts or programming for My Online Training Hub, Philip can be found answering questions on the Microsoft Power BI Community forums where he is a Super User.

More Excel VBA Posts

macro to center across selection
Use this Excel Center Across Selection shortcut macro generated by ChatGPT instead of merge and center.
Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts
Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables
PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel
Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list
Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.


Category:

Reader Interactions

Comments

  2. Thaddeus Lesnik

    I’d love to have a user menu box with two drop down lists, each populated with the list of styles.
    The purpose of having two drop down boxes would be to select one style from the list then in listbox 1 then choose a different style from the list to replace it with using listbox 2. This would be useful if, for example, I have a style which got duplicated when I copy a sheet and the style intent is the same, but two slightly different name exist.

    Reply

    • Catalin Bombea

      Hi Thaddeus,
      You can use this Custom UI editor, or the RibbonX Visual Designer addin for excel, make sure you scroll all the way down and choose the version that fits to your office: 32 or 64 bit add-in download.
      This last version will assist you in adding any type of control, inserts callbacks as well, all you have to do is to fill in the codes for populating the dropdowns in the callback code for that dropdown.

      Reply

  3. Diane Grahl

    This isn’t working for me, I keep a Debug. I seriously need to get rid of styles that come from another companies workbooks and have the ability to change other non-related workbooks. It’s so annoying, can I send you a copy of one?

    Reply

  4. Renny Schweiger

    Nice post. I’m always surprised that I usually learn something that’s often different from the post topic. In this case it was the counting down as you are deleting the things you are stepping through.

    How did you format your code (black background with the colours)? Is this native to Excel 2016 or Office 365? Or did you cut and paste the code into some other code formatting app?

    Also, one minor quibble: You used Range.Offset([RowOffset], [ColumnOffset]) with only one parameter, and later with two. I find this type of thing unnecessarily confusing and makes things more difficult to parse/read later on. I costs nearly nothing to add the “, 0” as the second parameter, and then it is very clear that you are staying in the same column and moving through the rows. This is particularly important with a post that is teaching to probably neophytes. Personal preference maybe, but I aim for readability. That’s why I’ve also disliked k += 1 in favour of k = k + 1 in VB. To me it smacks of using shortcuts and minimal code to either be cool (I know the ins and outs of this language) or being lazy (typing less). But I’m an old fart, set in my ways….

    Thanks again for all your work.

    Renny

    Reply

    • Philip Treacy

      Hi Renny,

      I’m using Visual Studio Code https://code.visualstudio.com and copy/paste the code in/out of the VBA editor. VSC looks so much nicer though so I used that for the screenshots. Would be nice if the in-built VBA editor looked like this.

      I get your point about Offset, I guess I’m just used to using the one parameter when I can. But as you say, using both might be useful for someone who’s learning.

      I actually love operators like +=, when I learned C, using ++ to increment a counter was awesome. It made it more enjoyable for me to write the code. I look at it that if one can learn to write x = x + 1 then one can also learn x += 1 or x++

      Cheers

      Phil

      Reply

  5. jim

    I think they breed when sheets are moved between workbooks
    I sometimes get workbooks with many tens of thousands of custom styles and have a similar macro to delete them:-

    For Each Style In Application.ActiveWorkbook.Styles
    If Not Style.BuiltIn Then Style.Delete
    Next Style

    Occasionally there are some stubborn ones that just won’t budge – usually the style name is garbage with control characters (eg “LÓÄþÍN^NuNVþˆHÁ(n” – without quotes) but one example I have in a file today is “Normal_Nat. Aggs Jan’13 Act EBITDA. v Prior Yr”
    I can’t delete or rename them manually or with a macro (“Delete method of Style class failed”),
    but I can duplicate and modify them
    If I manually create a new style with the above name then it can be deleted, so it’s not the names per se (unless there’s hidden characters?)
    Not in themselves a problem, they just look untidy (and I know they’re still there)

    Reply

    • Philip Treacy

      Hi Jim,

      Your code is using a FOR .. EACH loop, can you delete one of these troublesome styles by using its name or its number, e.g.

      ActiveWorkbook.Styles(“Cool Style 1”).Delete

      ActiveWorkbook.Styles(33).Delete

      I don’t see why it would matter but maybe there’s something about using the Styles object in this way that might work differently ?

      regards

      Phil

      Reply

      • jim again

        I’ve been told that this has been “fixed” since 2013
        but, given that you’re still publishing articles about it, I have my doubts

        Reply

Leave a Reply

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

Current ye@r *