How do I delete styles?

[Dave H]

I have used a macro to remove corrupt styles and removed 35k of them. My file still has a few hundred styles that I can't seem to delete. When I right click on the style and select delete nothing happens. If I click apply it returns the message Stlyle '2 2' not found. 2 2 was the style I was trying to apply. I am getting some weird characters in my styles as well. How do I delete these unwanted and unused styles?

Thanks

[xladept]

The help file offers this:

Please Login or Register  to view this content.

And this will list them:

Please Login or Register  to view this content.

[Dave H]

THe first code didn't appear to do anything. The second code changed a bunch of cells in a pivot table to these weird format names. Do pivot tables require these weird formats? I am trying to clean up a spreadsheet and this just doesn't seem right. Thanks for any advice.

[xladept]

Well the second code should have written the names of the styles in whatever sheet you had activated - perhaps you should have used a blank sheet for that?

[Dave H]

Ok, it doesn't matter to me where they get written. How do I delete them after that?

[xladept]

After you list them and remove any that you want to keep from the list - let's say your list is in column A

Please Login or Register  to view this content.

[Dave H]

When I try your sub DelStyles I get an error. Run-time error '1004': Delete method of Style class failed When I debug "ActiveWorkbook.Styles(S).Delete" is highlighted

What am I doing wrong? The styles are listed in column A. Thanks for your help.

[xladept]

Hi Dave,

I listed the styles that I have and deleted the "Bad" style - there was no hitch

[Dave H]

I was searching out the error message and it does seem to be related to deleting styles. For some reason these styles will not delete. Here is an example of some of the styles that won't delete.
2 3 2
 2 3 2
2 3 3
 2 3 3
2 3 4
 2 3 4
2 4
 2 4
2 4 2
 2 4 2
"콤냡?<$??: `1_1 "
콤마 [0]_
콤마_
C￥AØ_ FAB AIA¤
Ç¥ÁØ_´ëºñÇ¥ (2)_1_ºÎ´ëÅä°ø
C￥AØ_´eºnC￥ (2)_ºI´eAa°ø
Ç¥ÁØ_´ëºñÇ¥ (2)_ºÎ´ëÅä°ø
C￥AØ_¸AAa.¼OAI
Ç¥ÁØ_»ç¾÷ºÎº° ÃÑ°è


Some of the special charaters didn't copy.

[Dave H]

I have attached a small spreadsheet with these pesky styles. If you can figure out how to delete them that would be awesome. I have tried everything I know. Thanks

[shg]

Save in symbolic link (SLK) format, close, and reopen.

[xladept]

@ shg - Wow! Thanks! - I was going to call in the Cavalry!

(How many rep points do I now bestow?)

[shg]

Thanks -- It will only work for a single-worksheet workbook.

[Dave H]

Any other suggestions since my workbook that I am trying to clean up is about 30 worksheets with numerous complexities that would make it a real pain and probably weeks worth of rework if I had to do each sheet then rebuild.

[shg]

Maybe https://sergeig888.wordpress.com/2011/03/21/net4-0-version-of-the-xlstylestool-is-now-available/

Haven't used it, so I can't make a recommendation.

I did something else that worked, but can't promise it won't make your problem worse.

Save a new workbook by some name ("scratch.xlsx"), change the extension to zip, and open it. Navigate to xl/styles.xml and copy that to your desktop.

Change the extension of your problematic file to zip, replace styles.xml with the one from the new file, rename as xlsx, and open. After Excel stops whining about repairs, the bad styles are gone.

[Dave H]

shg

Its worth a shot, I'll give it a try. I couldn't get your link to open.

Thanks for the help.
Dave

[shg]

You're welcome, good luck.

[Dave H]

shg,

I'll give it a try. I can't seem to get your link to open though.
Thanks for your help!

Dave

[shg]

I can't seem to get your link to open though.

Dunno, Dave, just checked it again and it works fine for me.