clear format problem

[thehowlerr]

I am trying to write a macro to clear ALL format in a range of cell. However, I run into a problem.

I am using the Selection.ClearFormats command and it works in most cases, but fails when multiple format are applied to the cell.

For example if a cell only contains:

Hello wonderful world

than ClearFormats will remove the bold formatting. But if the cell contains

Hello wonderful world

than ClearFormats does not work and the word wonderful remains in bold.

Does anyone know how to clear format all the time.

Thanks

[mikerickson]

Would setting the Style to Normal work for you?

[geekgirlau]

I tried that - doesn't appear to work either!

[thehowlerr]

I tried that - doesn't appear to work either!

I had tried that as well and could not get it to work.

My only solution so far is to "copy-value" the cells to other cells. But that's ugly, especially since I really want to clear the format of the entire spreadsheet.

My real goal is to clear the format of the entire spreadsheet and then save it as XML to be able to machine-process the content. The Spreadsheet has multiple sheets, so I can't just save to CSV and read the value back in.

[GTO]

Hi there,

Have you tried addressing ea of the font's properties?

Sub FixCells()
Dim r As Range
Set r = Selection
With r.Font
.Bold = False
.ColorIndex = -4105
.Italic = False
.Name = Application.StandardFont
.Size = Application.StandardFontSize
.Strikethrough = False
.Subscript = False
.Superscript = False
.Underline = False
End With
End Sub


I amy well be missing something, but if its wacky formatting like:

Here's some wacky text.

This seems to take care of it.

Hope that helps,

Mark

[mikerickson]

From the Keyboard:
Copy
Clear Formats
PasteSpecial Values

In a Macro:
Sub Macro2()
Dim tempArray As Variant

With Selection
tempArray = .Value
.Clear
.Value = tempArray
End With
End Sub