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.
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 running this we get, not surprisingly, a list of styles in the workbook, and custom created styles are marked as such.
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.
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.
Thank you! Very helpful.
You’re welcome Mike
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.
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.
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?
Hi Diane,
Please start a forum post and attach your workbook to that.
Regards
Phil
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
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
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)
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
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