Wow ! Excel 7 has a secret macro "RemoveAllFormatting". Where is it in 2000 !?
Charles Jordan
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
Leo Heuser
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()
'leo.h...@get2net.dk 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" <charles...@btopenworld.com> skrev i en meddelelse
news:f79a4997.03080...@posting.google.com...
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
Leo Heuser
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.
Charles Jordan
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
Leo Heuser
You're welcome, Charles.
> (1) Is there any chance that we could see the code for "I made a
> similar routine for removing *unused* numberformats from a workbook".
Of course. It's not a secret routine <g>. You can find it at:
http://j-walk.com/ss/excel/eee/eee007.txt
Even if, I have had many mails telling me, that it
works OK, some users had problems with it, so
I made a version 1.01. You can find it, along with the
debate, by making a group search in Google.
Search the groups: microsoft.public.excel.*
Search for: DeleteUnusedCustomNumberFormats
>
> (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)
Apart from my former comments, I'm afraid, not.
Charles Jordan
Leo - as with all my other experiences with this NG, your info has
been absolutely invaluable. Tks. Charles
Leo Heuser
Thanks for the feedback!
LeoH