How with VBA can delete many line feed?

[akokin]

How with VBA can delete many line feed and spaces in the rows of table (Word)?

[fumei]

Please clarify this. You need to be clear and exact. Do you want to delete ALL spaces, in ALL the rows in the table?

If possible, post a document showing the situation.

[akokin]

OK. I have a table (5 columns and 6 rows). Some of the cells of table has line feed (one or more) and spaces before or after the text into cells.
I need delete and line feeds and all spaces from cells.
Thank you very much.

I have put the file (Onecell.zip).

[fumei]

I am going to let some of those who have been working on understanding Ranges have a go at this. Using ranges inside table cells can be tricky.

Here is a puzzler question.

What situation makes a Trim statement add characters?

ParaRange.Text = Trim(Para.Range.Text)

where ParaRange.Text = Onecell<space><space>

and

Trim(Para.Range.Text) = OneCell<space><space>vbCrLf

Using Trim adds a paragraph mark.

However, if the Range is made a Selection, then Trim does NOT add a paragraph mark.

where ParaRange.Text = Onecell<space><space>

ParaRange.Select
Selection.Text = Trim(Selection.Text)

now....

ParaRange.Text = Onecell

Have fun. With the sample file the OP supplied, this is not a trivial exercise.

akokin, what have YOU tried?

[TonyJollans]

Select the table.

Press Ctrl+H

In the Find what box enter [^13 ] (that's left (square) bracket, caret, one, three, space, right bracket)

Press "More..."
Check "Use Wildcards"
Press "Replace All"

Reply "No" to the prompt.

That's it.

Record it if you want VBA.

BUT ... As Gerry suggests, I very much doubt that is what you really want.

[TonyJollans]

Gerry,

On a quick check I don't get quite what you get with the Selection but an interesting scenario. I will try and look at it properly later.

[mdmackillop]

I am going to let some of those who have been working on understanding Ranges have a go at this. Using ranges inside table cells can be tricky. Now who could he mean? :whistle:

Option Explicit
Sub Tidy()
Dim cel As Cell, MyR As Range
Application.ScreenUpdating = False
With ActiveDocument.Tables(1).Range
With .Find
.Text = "^p"
.Replacement.Text = ""
End With
.Find.Execute Replace:=wdReplaceAll
For Each cel In .Cells
Set MyR = cel.Range
MyR.MoveEnd Unit:=wdCharacter, Count:=-1
cel.Range.Text = Trim(MyR.Text)
Next
End With
Application.ScreenUpdating = True
End Sub

[akokin]

Thank you all.
I did (my friend helped me) more difficulty:

Sub tableRow()

Dim myTable As Table
Dim myRange As Range
Dim myCell As Cell
Dim myColumns As Columns
Dim myRows As Rows

Dim ixCol As Long
Dim ixRow As Long

For Each myTable In ActiveDocument.Tables
Set myColumns = myTable.Columns
Set myRows = myTable.Rows

For ixCol = 1 To myColumns.Count
For ixRow = 1 To myRows.Count

Set myCell = myTable.Cell(ixRow, ixCol)
Set myRange = myCell.Range
Call myRange.MoveEnd(wdCharacter, -1)

Call ClearRange(myRange)
Set myRange = Nothing
Set myCell = Nothing

Next ixRow
Next ixCol

Set myColumns = Nothing
Set myRows = Nothing

Next myTable
End Sub
Sub ClearRange(r As Range)
If r.End = r.Start Then
Exit Sub
End If

Dim myFind As Find

Set myFind = r.Find

myFind.Text = "^p"
myFind.Replacement.Text = " "
myFind.Forward = True
myFind.Wrap = wdFindStop
myFind.Execute Replace:=wdReplaceAll

Set myFind = Nothing

r.Text = ClearText(r.Text)
End Sub
Function ClearText(s As String)

s = Trim(s)

Do While InStr(s, String(2, " ")) > 0
s = Replace(s, String(2, " "), " ")
Loop

ClearText = s
End Function

But the code of mdmackillop I like more.

[mdmackillop]

Hi Akokin,
Well done in achieving the result you were after. Both codes are basically following the same methodology.
Note that you don't need to reset MyRange/MyCell to nothing within the loop, but you can do this at the end of the code.

[akokin]

Thank you!

[fumei]

I like Tony's solution the best.

From Malcolm's code. For a laugh/test, try changing:cel.Range.Text = Trim(MyR.Text)


to:MyR.Text = Trim(MyR.Text)

[fumei]

Also akokin, you really should be very specific, and explicit, when you ask questions. It seems that you are OK with what you got for responses.

But, for me, I still would like to know if you seriously mean ALL spaces.

Tony's Find/Replace works very well, but do you really want:

One cell

That is: One<space>cell

to become Onecell (no space)? Which is what Tony's suggestion will do. And yes, that would be VERY easy to do with VBA by simply recording the Find/Replace action.

[akokin]

Sorry for my bad English, but I meaned deleting all unnecessary spaces before and after some text into cells. But not within of text (including one or several word). Thank you very much!

[akokin]

Hi. This is the code that I revised for my request. Thank you for your answers. It's work!

Sub tableClear()
Dim myTable As table
Dim myCell As cell
Dim myRange As Range

Application.ScreenUpdating = False

For Each myTable In ActiveDocument.Tables
myTable.AutoFitBehavior wdAutoFitWindow

With myTable.Rows(1)
.HeadingFormat = True
.HeightRule = wdRowHeightAuto
End With

With myTable.Range
.Find.ClearFormatting
.Find.text = "^p"
.Find.Replacement.text = ""
.Find.Forward = True
.Find.Wrap = wdFindContinue
.Find.Execute Replace:=wdReplaceAll

For Each myCell In .Cells
Set myRange = myCell.Range
myRange.MoveEnd Unit:=wdCharacter, Count:=-1
myCell.Range.text = Trim(myRange.text)
Next myCell

End With

Next myTable

Application.ScreenUpdating = True

End Sub