Help with macro to search and replace...

[Julie]

I'm trying to do something that I think should be relatively easy, but haven't figured out the trick yet

I have a table in Word that I created from an external program that has some cells with data that have a newline and then the end-of-cell marker. I want to delete the newline in those cases, but haven't figured out how yet

Any suggestions or help?

 

[Ralph Gottschalg]

if you have only one line feed try a search '^l' and replace with '', or
search for '^p' if your new line is a new paragraph

 

[Klaus Linke]

Hi Julie,

Unfortunately, you can't use "Find/Replace" to look for end-of-cell markers
(... they do seem to have the ASCII code 7, but looking for ^7 doesn't find
them).

So you'd need to loop the cells and delete the "newlines". I assume you mean
"manual line breaks" with "newlines"? If you mean paragraph marks ¶ instead,
replace Chr(11) with vbCr in the macro below.

The macro works on all selected cells. You could pretty easily change it to
work on all cells in the current table (For Each myCell In
Selection.Tables(1).Cells), or each cell in each table in the document (For
each myTable in ActiveDocument.Tables¶ For each myCell in
myTable.Range.Cells¶ ...)

Regards,
Klaus


Dim myCell As Cell
Dim myRange As Range
For Each myCell In Selection.Cells
Set myRange = myCell.Range.Characters.Last.Previous
While myRange.Text = Chr(11)
myRange.Delete
Set myRange = myCell.Range.Characters.Last.Previous
Wend
Next myCell

 

[Julie]

Thanks Klaus, that worked perfectly. I actually managed to wade through it myself and came up with something similar, but yours was more compact. =

Julie

 

[Ruby Tuesday]

Klaus would you mind posting the code to the newsgroup. I'd love to see it
and learn about it(i'm a newbie). Thanks

 

[Klaus Linke]

Klaus would you mind posting the code to the newsgroup.

Hi Ruby,

It was at the end of the previous message.
Slightly changed so it runs on all cells in all tables, deletes manual line
breaks and paragraph marks at the end of the cell, and doesn't balk in empty
cells:

Dim myCell As Cell
Dim myTable As Table
Dim myRange As Range
For Each myTable In ActiveDocument.Tables
For Each myCell In myTable.Range.Cells
Set myRange = myCell.Range.Characters.Last.Previous
If myRange Is Nothing Then
' empty cell: do nothing
Else
While myRange.Text = Chr(11) Or myRange.Text = vbCr
myRange.Delete
Set myRange = myCell.Range.Characters.Last.Previous
Wend
End If
Next myCell
Next myTable

In case you have trouble making the code work, see
http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm

Greetings,
Klaus