Losing formatting using .Text to replace

treva31 · #1 09-30-2014, 07:17 PM

Hi,

I have this little sub that removes leading and trailing spaces from text in cells of all my tables.

It works great but it turns any coloured text to black.

Is there a way to keep the formatting?

Thanks.

Code:

Sub RemoveSpaces() 
    Dim oRng As Range 
    Dim oTable As Table 
    Dim acell As Cell 
    For Each oTable In ActiveDocument.Tables 
        With oTable 
            For Each acell In oTable.Range.Cells 
                Set oRng = acell.Range 
                oRng.End = oRng.End - 1 
                oRng.Text = Trim(oRng.Text) 
            Next acell 
        End With 
    Next oTable 
End Sub

macropod · #2 09-30-2014, 09:21 PM

Try:

Code:

Sub RemoveSpaces()
    Dim oTable As Table, acell As Cell, oRng As Range
    For Each oTable In ActiveDocument.Tables
        With oTable
            For Each acell In oTable.Range.Cells
                Set oRng = acell.Range
                With oRng
                  .End = .End - 1
                  While .Characters.Last = " "
                    .Characters.Last = ""
                  Wend
                  While .Characters.First = " "
                    .Characters.First = ""
                  Wend
                End With
            Next acell
        End With
    Next oTable
End Sub

PS: When posting code, please use the code tags. They're on the 'Go Advanced' tab at the bottom of this screen. Your post tried to replicate them as <CODE></CODE>, whereas the tags here use [ and ] instead of < and >.

treva31 · #3 09-30-2014, 10:31 PM

Thats perfect thank you!

Yea sorry the code button doesnt seem to be working for me.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
replace letters by font without losing format	romanticbiro	Word VBA	31	03-16-2017 04:00 PM
Find and Replace, Formatting, Formatting Formula	kjxavier	Word	1	07-04-2014 09:11 AM
How to stop losing formatting	MuffinRider	Word	1	02-28-2014 04:27 AM
Word Documents Losing Formatting/Styles When Reopened	jleary	Word	2	02-05-2013 12:27 PM
Replace formatting with text	eyehefbee	Word	2	11-09-2009 02:41 AM