• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

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

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Listing and Deleting Custom Styles

You are here: Home / Excel VBA / Listing and Deleting Custom Styles
listing and deleting custom styles
September 11, 2018 by Philip Treacy

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.
Please enter a valid email address.

Download the workbook. Note: This is a .xlsm file. Please ensure your browser doesn't change the file extension on download.

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

More Excel VBA Posts

Display All Matches from Search in Userform ListBox

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

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

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

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

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

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

Multi-Language Excel Calendar (Date Picker) for 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

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

Searching for Data With a User Form

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.
Checking values in range objects with vba

Checking Values in Range Objects With VBA

Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.


Category: Excel VBA
Previous Post:Excel HashExcel Hash Interactive Bug Menu
Next Post:Remove Formatting from a Tableremove formatting from a table

Reader Interactions

Comments

  1. Mike

    September 6, 2020 at 5:37 am

    Thank you! Very helpful.

    Reply
    • Philip Treacy

      September 7, 2020 at 10:49 am

      You’re welcome Mike

      Reply
  2. Thaddeus Lesnik

    August 10, 2019 at 12:08 am

    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

      August 12, 2019 at 3:19 pm

      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

    September 26, 2018 at 5:16 am

    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
    • Philip Treacy

      September 26, 2018 at 7:59 am

      Hi Diane,

      Please start a forum post and attach your workbook to that.

      Regards

      Phil

      Reply
  4. Renny Schweiger

    September 12, 2018 at 10:57 pm

    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

      September 13, 2018 at 10:46 am

      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

    September 12, 2018 at 8:58 pm

    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

      September 13, 2018 at 11:14 am

      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

        September 27, 2018 at 8:17 pm

        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 Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.