# Macro to clear formatting



## newtomacros (Jul 20, 2009)

Hi

I recoreded the following macro to clear the formatting of a piece of searched tag text. How do i get the macro to loop so it finds all occurences in the whole document.

Selection.Find.ClearFormatting
With Selection.Find
.Text = "\<\cf cstyle?*\>"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchKashida = False
.MatchDiacritics = False
.MatchAlefHamza = False
.MatchControl = False
.MatchByte = False
.CorrectHangulEndings = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchFuzzy = False
.MatchWildcards = True
End With

Selection.Find.Execute
Selection.ClearFormatting
Selection.ClearFormatting


----------



## macropod (Apr 11, 2008)

Hi newtomacros,

Try:

```
Sub Demo()
With ActiveDocument.Content.Find
  .ClearFormatting
  .Replacement.ClearFormatting
  .Text = "\<\cf cstyle?*\>"
  .Replacement.Text = ""
  .Forward = True
  .Wrap = wdFindContinue
  .Format = False
  .MatchCase = False
  .MatchWholeWord = False
  .MatchAllWordForms = False
  .MatchSoundsLike = False
  .MatchWildcards = True
  .Execute Replace:=wdReplaceAll
End With
End Sub
```


----------



## newtomacros (Jul 20, 2009)

Unfortunately this removes the found tags instead of clearing the formatting


----------



## macropod (Apr 11, 2008)

Hi newtomacros,

Not being familiar with your HTML code, I interpreted "\<\cf cstyle?*\>" as representing a formatting tag that you wanted to delete. Perhaps you could clarify what it is you want to achieve:
1. Remove character formatting that's been applied to the string represented by the "\<\cf cstyle?*\>" expression?
2. Replace some part of the "\<\cf cstyle?*\>" expression with something else?
3. Something else?


----------



## macropod (Apr 11, 2008)

Hi newtomacros,

Please see: http://www.excelguru.ca/node/7

I am not impressed at finding that, while I've been trying to help you solve the problem here, you're asking others to traverse the same ground at: http://www.vbaexpress.com/forum/showthread.php?t=27706. I'm sure fumei over there won't be impressed either.

Please, if you're going to pose the same question in multiple forums, at least have the courtesy of saying so.


----------



## newtomacros (Jul 20, 2009)

I apologise if you take offense to me posting my question to other forums. 

I was not aware of this golden rule but in my experience two forums are better than one.

If I am not discommunicated from this form then it is option 1 that I am trying to achieve.

1.Remove character formatting that's been applied to the string represented by the "\<\cf cstyle?*\>" expression?

Thanks


----------



## fumei (Jul 22, 2009)

No...I am not impressed.

I will reiterate what I, and macropod, have asked.

What EXACTLY is the "\<\cf cstyle?*\>" expression? Please give an example. Describe it. Because as it stands this is being interpreted as a string literal, an ACTUAL string.

.Text does not test against any format whatsoever.


----------



## macropod (Apr 11, 2008)

Hi newtomacros,

I have no objection to you posting in other forums - just the lack of courtesy to all those involved who are being asked to work out what your requirements are, without the benefit of knowing what's already been dealt with elsewhere.

So far, you haven't clearly expressed what you need help with. What formatting has been applied to the string represented by the "\<\cf cstyle?*\>" expression? And what exactly do you want that replaced with?


----------



## newtomacros (Jul 20, 2009)

I will try to explain.

Here is an example of the text in the DOC file.

<paragraph prst="[No paragraph style]" ><cf cstyle="[No character style]" ptfs="c_45 Light" ptsz="U_5" font="c_Frutiger">Save at</cf><cf cstyle="Normal" ptfs="c_45 Light" ptsz="U_5" szld="U_6" plng="k_English: UK" font="c_Frutiger" jcal="e_Jabl">IMP Bank</cf></paragraph>

I am using word search with wild cards ("\<\cf cstyle?*\>") to find the following and when found remove the formatting by selecting”Clear Formatting” from the toolbar.

<cf cstyle="[No character style]" ptfs="c_45 Light" ptsz="U_5" font="c_Frutiger">

And

<cf cstyle="Normal" ptfs="c_45 Light" ptsz="U_5" szld="U_6" plng="k_English: UK" font="c_Frutiger" jcal="e_Jabl">

These pieces of text can be bold, italic, coloured, different fonts, etc…. What I am trying to achieve by the macro is to search the doc for this type of text irrespective of the format and once found remove the formatting so the text is back to its original format.

The macro I recorded and posted in my first post works as expected but it stops after it finds the first instance of text as it does not loop through the whole document.

Hope this helps


----------



## macropod (Apr 11, 2008)

Hi newtomacros,

Try:

```
Sub Demo()
With ActiveDocument.Content.Find
  .ClearFormatting
  With .Replacement
    .ClearFormatting
    With .Font
      .Bold = False
      .Italic = False
      .Underline = wdUnderlineNone
      .Color = wdColorAutomatic
      .Name = "Arial"
    End With
  End With
    .Text = "\<\cf cstyle?*\>"
    .Replacement.Text = "^&"
    .Forward = True
    .Wrap = wdFindContinue
    .Format = True
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = True
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    .Execute Replace:=wdReplaceAll
End With
End Sub
```
Note: You need to specify the replacement font if you want it to be anything other than the current font; I've nominated Arial but you can use something else if you prefer. If you're happy with the current font, simply nominate the font as "".

As for your statement that:


> The macro I recorded and posted in my first post works as expected


it in fact deleted the found text, as evidenced by the line '.Replacement.Text = ""'. That's not quite the same as clearing the text's formatting...


----------



## newtomacros (Jul 20, 2009)

Thanks for the macro, it is just what i was looking for. I appreciate the help.

If can be bold and ask, if i want to set the style to Normal where would i put this in the macro.

Thanks


----------



## macropod (Apr 11, 2008)

Hi newtomacros,

The 'Normal' Style would apply to the whole paragraph, not to just the found text string. If all you wanted to do is to convert everything in the paragraph with the found text to Normal Style, you'd code the macro differently - you can use the macro recorder to get the code - but on its own it wouldn't be enough to change the formatting of the found text.


----------



## fumei (Jul 22, 2009)

I am just wondering. If various manually formatted text is an issue, why not simply clearformat the entire document?

Ctrl-A, select Clear Formatting from the style dropdown.

This removes all formatting, including manual character formatting within paragraphs, and makes everything "Normal" (however Normal is defined).

To do via code:

```
Sub ClearEverything()
With Selection
   .WholeStory
   .ClearFormatting
   .Collapse 1  ' puts cursor at start of doc
End With
End Sub
```
Aside: one of the few instances where you have to use Selection rather than Range.


----------



## macropod (Apr 11, 2008)

Hi fumei,

I had thought of that too, but decided to limit? my creativity to clearing just what was asked for. FWIW, the OP could do that without a macro, by selecting the whole document then using Cut, followed by Edit|Paste Special|Unformatted text.


----------



## fumei (Jul 22, 2009)

Regarding "without a macro"... Ctrl-A, click Clear Formatting seems simpler to me. But what the heck.


----------



## newtomacros (Jul 20, 2009)

The DOC files contains many different styles of format for different pieces of text that is why I need to only change specific pieces of text back to its original format.

I also discovered that if you record the macro to replace the searched text and set the style of the replaced text to “Default Paragraph” then this changes the searched text back to its original format. (What I was looking for)

Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
Selection.Find.Replacement.Style = ActiveDocument.Styles( _
"Default Paragraph Font")
With Selection.Find
.Text = "\<\cf cstyle?*\>"
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = True
.MatchCase = False
.MatchWholeWord = False
.MatchKashida = False
.MatchDiacritics = False
.MatchAlefHamza = False
.MatchControl = False
.MatchByte = False
.CorrectHangulEndings = False
.MatchAllWordForms = False
.MatchSoundsLike = False
.MatchFuzzy = False
.MatchWildcards = True
End With
Selection.Find.Execute Replace:=wdReplaceAll

Thanks all for the support


----------

