"Leo Heuser" wrote in message ...
Charles

To delete all unused styles try the sub below.
It will also delete the default styles (except "Normal"), if they are not
used in the workbook.
If you want them back, the easiest way is to open a new workbook (say
Book2), activate the
original workbook, choose Format Styles, push the merge button and choose
Book2.

Sub DeleteUnusedStyles()
November 1999
Dim Sh As Object
Dim sStyle As Variant
Dim nStyle() As Variant
Dim xStyle As Long
Dim Counter As Long
Dim Counter1 As Long
Dim Counter2 As Long
Dim StartRow As Long
Dim EndRow As Long
Dim Dummy As Variant
Dim pPresent As Boolean
Dim Answer
Dim c As Object
Dim DataStart As Long
Dim DataEnd As Long
Dim AnswerText As String
ReDim nStyle(1 To ActiveWorkbook.Styles.Count)

AnswerText = "Do you want to delete unused styles from the workbook?"
AnswerText = AnswerText & Chr(10) & _
"To get a list of used and unused styles only, choose No."
Answer = MsgBox(AnswerText, 259)
If Answer = vbCancel Then GoTo Finito

On Error GoTo Finito
Worksheets.Add.Move After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = "CustomStyles"
Worksheets("CustomStyles").Activate
For Counter = 1 To ActiveWorkbook.Styles.Count
nStyle(Counter) = ActiveWorkbook.Styles(Counter).Name
Next Counter

Range("A1").Value = "Styles"
Range("B1").Value = "Styles used in workbook"
Range("C1").Value = "Styles not used"
Range("A1:C1").Font.Bold = True

StartRow = 3
EndRow = ActiveSheet.Rows.Count

For Counter = 1 To UBound(nStyle)
Cells(StartRow, 1).Offset(Counter - 1, 0).Value = nStyle(Counter)
Next Counter

Counter = 0
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Name = "CustomStyles" Then Exit For
For Each c In Sh.UsedRange.Cells
sStyle = c.Style.Name
If Application.WorksheetFunction. _
CountIf(Range(Cells(StartRow, 2), _
Cells(EndRow, 2)), sStyle) = 0 Then
Cells(StartRow, 2).Offset(Counter, 0).Value = sStyle
Counter = Counter + 1
End If
Next c
Next Sh

xStyle = Range(Cells(StartRow, 2), Cells(EndRow, 2)).Find("").Row - 2

Counter2 = 0
For Counter = 1 To UBound(nStyle)
pPresent = False
For Counter1 = 1 To xStyle
If nStyle(Counter) = Cells(StartRow, 2). _
Offset(Counter1 - 1, 0).Value Then
pPresent = True
Exit For
End If
Next Counter1
If pPresent = False Then
Cells(StartRow, 3).Offset(Counter2, 0).Value = nStyle(Counter)
Counter2 = Counter2 + 1
End If
Next Counter
With ActiveSheet.Columns("A:C")
.AutoFit
.HorizontalAlignment = xlLeft
End With
If Answer = vbYes Then
DataStart = Range(Cells(1, 3), Cells(EndRow, 3)).Find("").Row + 1
DataEnd = Cells(DataStart, 3).Resize(EndRow, 1).Find("").Row - 1
On Error Resume Next
For Each c In Range(Cells(DataStart, 3), Cells(DataEnd, 3)).Cells
ActiveWorkbook.Styles(c.Value).Delete
Next c
End If
Finito:
Set c = Nothing
Set Sh = Nothing
End Sub



--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

"Charles Jordan" skrev i en meddelelse
om...
Hi all. (Windows ME) I have been having horrendous memory problems in
both XL95 and XL2000 with the dreaded "Too many formats" error message
aborting proceedings. BUT by accident I just fell over a secret,
undocumented MS subroutine entitled "RemoveAllFormatting". Its results
are nothing less than spectacular.

I first of all tried :-

(a) deleting 50-60 custom formats
(b) deleting an enormous number of accumulated styles attached
variously to worksheets and Dialog boxes..

But even after these the code has been hovering only just under MS's
secret memory resource limits, a few extra formats (1-3), and bam. A
nightmare. Then I tripped over "RemoveAllFormatting".

However the new routine (see below) seems to release a large amount of
resources, BUT it does NOT run in 2000, and it is documented by MS but
NOWHERE.

Here it is :-

'----------------------------------------------------
Sub UnformatSelection()
With Selection
Application.Run Macro:="RemoveAllFormatting"
End With
End Sub
'----------------------------------------------------

Does any one know where this code is actually lurking in XL7, and if
so where, in XL2000/2002 ?

TIA

Charles Jordan

Thanks Leo - it works very well. But it probably doesn't remove all
the cell formatting, does it ? Not quite clear from your message. If
so, do we not still need the following code, (or whatever is the
XL2000 equivalent ) ?

With Selection
Application.Run Macro:="RemoveAllFormatting"
End With

Thanks - Charles

"Charles Jordan" skrev i en meddelelse
om...
"Leo Heuser" wrote in message
...
Charles

To delete all unused styles try the sub below.
It will also delete the default styles (except "Normal"), if they are
not
used in the workbook.
If you want them back, the easiest way is to open a new workbook (say
Book2), activate the
original workbook, choose Format Styles, push the merge button and
choose
Book2.

Sub DeleteUnusedStyles()
November 1999
Dim Sh As Object
Dim sStyle As Variant
Dim nStyle() As Variant
Dim xStyle As Long
Dim Counter As Long
Dim Counter1 As Long
Dim Counter2 As Long
Dim StartRow As Long
Dim EndRow As Long
Dim Dummy As Variant
Dim pPresent As Boolean
Dim Answer
Dim c As Object
Dim DataStart As Long
Dim DataEnd As Long
Dim AnswerText As String
ReDim nStyle(1 To ActiveWorkbook.Styles.Count)

AnswerText = "Do you want to delete unused styles from the
workbook?"
AnswerText = AnswerText & Chr(10) & _
"To get a list of used and unused styles only, choose No."
Answer = MsgBox(AnswerText, 259)
If Answer = vbCancel Then GoTo Finito

On Error GoTo Finito
Worksheets.Add.Move After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = "CustomStyles"
Worksheets("CustomStyles").Activate
For Counter = 1 To ActiveWorkbook.Styles.Count
nStyle(Counter) = ActiveWorkbook.Styles(Counter).Name
Next Counter

Range("A1").Value = "Styles"
Range("B1").Value = "Styles used in workbook"
Range("C1").Value = "Styles not used"
Range("A1:C1").Font.Bold = True

StartRow = 3
EndRow = ActiveSheet.Rows.Count

For Counter = 1 To UBound(nStyle)
Cells(StartRow, 1).Offset(Counter - 1, 0).Value =
nStyle(Counter)
Next Counter

Counter = 0
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Name = "CustomStyles" Then Exit For
For Each c In Sh.UsedRange.Cells
sStyle = c.Style.Name
If Application.WorksheetFunction. _
CountIf(Range(Cells(StartRow, 2), _
Cells(EndRow, 2)), sStyle) = 0 Then
Cells(StartRow, 2).Offset(Counter, 0).Value = sStyle
Counter = Counter + 1
End If
Next c
Next Sh

xStyle = Range(Cells(StartRow, 2), Cells(EndRow, 2)).Find("").Row -
2

Counter2 = 0
For Counter = 1 To UBound(nStyle)
pPresent = False
For Counter1 = 1 To xStyle
If nStyle(Counter) = Cells(StartRow, 2). _
Offset(Counter1 - 1, 0).Value Then
pPresent = True
Exit For
End If
Next Counter1
If pPresent = False Then
Cells(StartRow, 3).Offset(Counter2, 0).Value =
nStyle(Counter)
Counter2 = Counter2 + 1
End If
Next Counter
With ActiveSheet.Columns("A:C")
.AutoFit
.HorizontalAlignment = xlLeft
End With
If Answer = vbYes Then
DataStart = Range(Cells(1, 3), Cells(EndRow, 3)).Find("").Row +
1
DataEnd = Cells(DataStart, 3).Resize(EndRow, 1).Find("").Row - 1
On Error Resume Next
For Each c In Range(Cells(DataStart, 3), Cells(DataEnd,
3)).Cells
ActiveWorkbook.Styles(c.Value).Delete
Next c
End If
Finito:
Set c = Nothing
Set Sh = Nothing
End Sub



--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

"Charles Jordan" skrev i en meddelelse
om...
Hi all. (Windows ME) I have been having horrendous memory problems in
both XL95 and XL2000 with the dreaded "Too many formats" error message
aborting proceedings. BUT by accident I just fell over a secret,
undocumented MS subroutine entitled "RemoveAllFormatting". Its results
are nothing less than spectacular.

I first of all tried :-

(a) deleting 50-60 custom formats
(b) deleting an enormous number of accumulated styles attached
variously to worksheets and Dialog boxes..

But even after these the code has been hovering only just under MS's
secret memory resource limits, a few extra formats (1-3), and bam. A
nightmare. Then I tripped over "RemoveAllFormatting".

However the new routine (see below) seems to release a large amount of
resources, BUT it does NOT run in 2000, and it is documented by MS but
NOWHERE.

Here it is :-

'----------------------------------------------------
Sub UnformatSelection()
With Selection
Application.Run Macro:="RemoveAllFormatting"
End With
End Sub
'----------------------------------------------------

Does any one know where this code is actually lurking in XL7, and if
so where, in XL2000/2002 ?

TIA

Charles Jordan

Thanks Leo - it works very well. But it probably doesn't remove all
the cell formatting, does it ? Not quite clear from your message. If
so, do we not still need the following code, (or whatever is the
XL2000 equivalent ) ?

With Selection
Application.Run Macro:="RemoveAllFormatting"
End With

Thanks - Charles

You're welcome, Charles.

Removing cellformats and removing formats from a workbook
are two different things.

The sub

Sub UnformatSelection()
With Selection
Application.Run Macro:="RemoveAllFormatting"
End With
End Sub

works on a selection (With Selection) in the active window. All
it does is calling another sub
"RemoveAllFormatting"
and from this sub, which BTW must reside in a module in
the same workbook as the sub "UnformatSelection()",
the actual removal of formats is carried out.

Removing cellformats might mean setting all numerical
data to the format "General" and all cells to the style
"Normal".

The sub could be something like this:


Sub RemoveAllFormatting()
With Selection
.NumberFormat = "General"
.Style = "Normal"
End With
End Sub

but running this sub *doesn't* remove any numberformats
or styles from the *workbook*.

My sub OTOH removes *unused* styles from the *workbook*.
I made a similar routine for removing *unused* numberformats
from a workbook.

I hope, you can use the above information.


Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

"Leo Heuser" wrote in message ...
"Charles Jordan" skrev i en meddelelse
om...
"Leo Heuser" wrote in message
...
Charles

To delete all unused styles try the sub below.
It will also delete the default styles (except "Normal"), if they are
not
used in the workbook.
If you want them back, the easiest way is to open a new workbook (say
Book2), activate the
original workbook, choose Format Styles, push the merge button and
choose
Book2.

Sub DeleteUnusedStyles()
November 1999
Dim Sh As Object
Dim sStyle As Variant
Dim nStyle() As Variant
Dim xStyle As Long
Dim Counter As Long
Dim Counter1 As Long
Dim Counter2 As Long
Dim StartRow As Long
Dim EndRow As Long
Dim Dummy As Variant
Dim pPresent As Boolean
Dim Answer
Dim c As Object
Dim DataStart As Long
Dim DataEnd As Long
Dim AnswerText As String
ReDim nStyle(1 To ActiveWorkbook.Styles.Count)

AnswerText = "Do you want to delete unused styles from the
workbook?"
AnswerText = AnswerText & Chr(10) & _
"To get a list of used and unused styles only, choose No."
Answer = MsgBox(AnswerText, 259)
If Answer = vbCancel Then GoTo Finito

On Error GoTo Finito
Worksheets.Add.Move After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Name = "CustomStyles"
Worksheets("CustomStyles").Activate
For Counter = 1 To ActiveWorkbook.Styles.Count
nStyle(Counter) = ActiveWorkbook.Styles(Counter).Name
Next Counter

Range("A1").Value = "Styles"
Range("B1").Value = "Styles used in workbook"
Range("C1").Value = "Styles not used"
Range("A1:C1").Font.Bold = True

StartRow = 3
EndRow = ActiveSheet.Rows.Count

For Counter = 1 To UBound(nStyle)
Cells(StartRow, 1).Offset(Counter - 1, 0).Value =
nStyle(Counter)
Next Counter

Counter = 0
For Each Sh In ActiveWorkbook.Worksheets
If Sh.Name = "CustomStyles" Then Exit For
For Each c In Sh.UsedRange.Cells
sStyle = c.Style.Name
If Application.WorksheetFunction. _
CountIf(Range(Cells(StartRow, 2), _
Cells(EndRow, 2)), sStyle) = 0 Then
Cells(StartRow, 2).Offset(Counter, 0).Value = sStyle
Counter = Counter + 1
End If
Next c
Next Sh

xStyle = Range(Cells(StartRow, 2), Cells(EndRow, 2)).Find("").Row -
2

Counter2 = 0
For Counter = 1 To UBound(nStyle)
pPresent = False
For Counter1 = 1 To xStyle
If nStyle(Counter) = Cells(StartRow, 2). _
Offset(Counter1 - 1, 0).Value Then
pPresent = True
Exit For
End If
Next Counter1
If pPresent = False Then
Cells(StartRow, 3).Offset(Counter2, 0).Value =
nStyle(Counter)
Counter2 = Counter2 + 1
End If
Next Counter
With ActiveSheet.Columns("A:C")
.AutoFit
.HorizontalAlignment = xlLeft
End With
If Answer = vbYes Then
DataStart = Range(Cells(1, 3), Cells(EndRow, 3)).Find("").Row +
1
DataEnd = Cells(DataStart, 3).Resize(EndRow, 1).Find("").Row - 1
On Error Resume Next
For Each c In Range(Cells(DataStart, 3), Cells(DataEnd,
3)).Cells
ActiveWorkbook.Styles(c.Value).Delete
Next c
End If
Finito:
Set c = Nothing
Set Sh = Nothing
End Sub



--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

"Charles Jordan" skrev i en meddelelse
om...
Hi all. (Windows ME) I have been having horrendous memory problems in
both XL95 and XL2000 with the dreaded "Too many formats" error message
aborting proceedings. BUT by accident I just fell over a secret,
undocumented MS subroutine entitled "RemoveAllFormatting". Its results
are nothing less than spectacular.

I first of all tried :-

(a) deleting 50-60 custom formats
(b) deleting an enormous number of accumulated styles attached
variously to worksheets and Dialog boxes..

But even after these the code has been hovering only just under MS's
secret memory resource limits, a few extra formats (1-3), and bam. A
nightmare. Then I tripped over "RemoveAllFormatting".

However the new routine (see below) seems to release a large amount of
resources, BUT it does NOT run in 2000, and it is documented by MS but
NOWHERE.

Here it is :-

'----------------------------------------------------
Sub UnformatSelection()
With Selection
Application.Run Macro:="RemoveAllFormatting"
End With
End Sub
'----------------------------------------------------

Does any one know where this code is actually lurking in XL7, and if
so where, in XL2000/2002 ?

TIA

Charles Jordan

Thanks Leo - it works very well. But it probably doesn't remove all
the cell formatting, does it ? Not quite clear from your message. If
so, do we not still need the following code, (or whatever is the
XL2000 equivalent ) ?

With Selection
Application.Run Macro:="RemoveAllFormatting"
End With

Thanks - Charles

You're welcome, Charles.

Removing cellformats and removing formats from a workbook
are two different things.

The sub

Sub UnformatSelection()
With Selection
Application.Run Macro:="RemoveAllFormatting"
End With
End Sub

works on a selection (With Selection) in the active window. All
it does is calling another sub
"RemoveAllFormatting"
and from this sub, which BTW must reside in a module in
the same workbook as the sub "UnformatSelection()",
the actual removal of formats is carried out.

Removing cellformats might mean setting all numerical
data to the format "General" and all cells to the style
"Normal".

The sub could be something like this:


Sub RemoveAllFormatting()
With Selection
.NumberFormat = "General"
.Style = "Normal"
End With
End Sub

but running this sub *doesn't* remove any numberformats
or styles from the *workbook*.

My sub OTOH removes *unused* styles from the *workbook*.
I made a similar routine for removing *unused* numberformats
from a workbook.

I hope, you can use the above information.


Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

Leo - thanks.
(1) Is there any chance that we could see the code for "I made a
similar routine for removing *unused* numberformats from a workbook".
?
Your code is pretty sophisticated and I'm sure the other NG members
like me would benefit greatly from it.

(2) I sill have not discovered where the Sub "RemoveAllFormatting"
actually is, and what else is there. Can you throw any light on it ?
(I stil refer to XL95, wher it first appeared)

V. many tks.. Charles