View Full Version : clear format problem
thehowlerr
12-10-2009, 05:44 PM
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
12-10-2009, 07:10 PM
Would setting the Style to Normal work for you?
geekgirlau
12-10-2009, 11:29 PM
I tried that - doesn't appear to work either!
thehowlerr
12-11-2009, 05:59 AM
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.
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
12-12-2009, 12:12 PM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.