R
Robin Hammond
If anyone is interested I thought I would have a go at a problem that's
annoyed me over the years.
Whenever you copy a sheet from one book to another, you also seem to get all
the styles from the source book copied across as well. As models evolve over
the years, there's an awful lot of clutter and redundancy as these styles
build up. The best I've seen so far is a workbook with 1,234 styles in it!
Of those, 1,217 were not even being used.
So, up on my site there's an open source offering - and suggestions are very
welcome, particularly about how to speed up the initial workbook scan - that
will.
1. Show you all the styles in a workbook (not difficult), and how many cells
are using them (more tricky).
2. Then allow you to delete all unused user-defined styles in one go (it's
one by one using the standard dialogs and it's not possible to tell if a
style is in use anywhere).
3. Change all cells with one style to another style of your choosing (rather
useful). This gets rid of the clutter. For example, in the amazing book with
all those styles, I found over a hundred variants on "Normal", all of which
could be mapped to the standard Normal format or deleted.
Limitations:
1. the progbar class used in the file won't work with 97 although it's not
difficult to take out.
2. no undo routine of course.
The file is here:
http://www.enhanceddatasystems.com/ED/Pages/ExcelStyleMapper.htm
Robin Hammond
www.enhanceddatasystems.com
annoyed me over the years.
Whenever you copy a sheet from one book to another, you also seem to get all
the styles from the source book copied across as well. As models evolve over
the years, there's an awful lot of clutter and redundancy as these styles
build up. The best I've seen so far is a workbook with 1,234 styles in it!
Of those, 1,217 were not even being used.
So, up on my site there's an open source offering - and suggestions are very
welcome, particularly about how to speed up the initial workbook scan - that
will.
1. Show you all the styles in a workbook (not difficult), and how many cells
are using them (more tricky).
2. Then allow you to delete all unused user-defined styles in one go (it's
one by one using the standard dialogs and it's not possible to tell if a
style is in use anywhere).
3. Change all cells with one style to another style of your choosing (rather
useful). This gets rid of the clutter. For example, in the amazing book with
all those styles, I found over a hundred variants on "Normal", all of which
could be mapped to the standard Normal format or deleted.
Limitations:
1. the progbar class used in the file won't work with 97 although it's not
difficult to take out.
2. no undo routine of course.
The file is here:
http://www.enhanceddatasystems.com/ED/Pages/ExcelStyleMapper.htm
Robin Hammond
www.enhanceddatasystems.com