PDA

View Full Version : Solved: Stubborn Styles (How To Remove Them?)



doctorp
06-12-2007, 12:30 PM
Hi All

This is a tricky one. I have a file which has accumulated a number of styles in it (200+). I have managed to remove them all, but there are some remaining which I can't fgure out how to remove. Any advice?

File Attached.

doctorp
06-12-2007, 12:31 PM
So obviously my wording is a little off. I have removed most of them, not all.

geekgirlau
06-12-2007, 08:00 PM
They're stubborn alright :dau:

I tried looping through the styles and deleting all but a couple of specific styles (such as Normal) - most gave me no error messages, but it didn't delete any of the styles either!

I don't know what to suggest here, but I can't think of another way to remove them - sorry!

lucas
06-12-2007, 08:49 PM
This might have something to do with it:
Sub a()
MsgBox ActiveWorkbook.Styles.Count & " Styles"
MsgBox ActiveWorkbook.Names.Count & " Defined Named Ranges"
End Sub
I get 29 Styles and 51 Named Ranges.
I can remove the named ranges with this from one of Jakes kb entries:

Sub RemNamedRanges()
Dim nm As Name
For Each nm In ActiveWorkbook.Names
nm.Delete
Next
End Sub

But I can't get this to work on Styles:
Sub RemStyle()
Dim st As Style
For Each st In ActiveWorkbook.Styles
st.Delete
Next
End Sub
I think you might be better off to copy the sheets to a new workbook..

geekgirlau
06-12-2007, 09:07 PM
I think you'll find if you copy the sheet, the style goes along for the ride ...

lucas
06-12-2007, 09:28 PM
I hadn't tried it but it appears that you are correct Anne...? I never use styles so I'm kinda at a loss..

geekgirlau
06-12-2007, 10:45 PM
Untested, but maybe you could copy the cells and do a paste formula. You can't paste formats as this copies the style. I guess it depends on how badly you want to get rid of the styles versus how much time it will take to reformat the workbook ...

doctorp
06-12-2007, 11:51 PM
Thanks for all your help so far. Reformatting the workbook would take days so I'd rather leave the stubborn styles behind than reformat the workbook. Its almost as if this is a bug in excel.

Ivan F Moala
06-13-2007, 02:55 AM
After removing named ranges ....
Try saving as Html file and then back again


see atttached

Ivan F Moala
06-13-2007, 02:56 AM
Sorry ... just in case .... do this on a Back up

lucas
06-13-2007, 04:18 PM
Saving it as html and back did work....I would never have thought to do that.

doctorp
06-14-2007, 12:08 AM
May the excel Gods bless you. A great solution which preserves all existing used styles, but also copies the normal template of styles back into the workbook. I just hope no serious functionality is lost through saving to HTML and back again. Thanks again.