Trailing Spaces in Merged Cells




I have a large spreadsheet with data in merged cells. When I look in Excel,
the trailing spaces doesn't appear and when I copied the data onto Notepad,
the spaces are shown. I have tried TRIM (works for single cells not merged
cells). Any help would be greatly appreciated.




Hi Meng,

Try the following macro:

Sub TrimRange()
Dim SBar As Boolean, Cell As Range, CellCount As Long, I As Long
SBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
Application.ScreenUpdating = False
Application.Calculation = xlManual
On Error Resume Next
I = 0
If Selection.Rows.Count * Selection.Columns.Count > 1 Then
CellCount = Selection.Rows.Count * Selection.Columns.Count
CellCount = ActiveSheet.UsedRange.Rows.Count * ActiveSheet.UsedRange.Columns.Count
End If
For Each Cell In Selection.SpecialCells(xlConstants)
Cell.Value = Application.Trim(Replace(Cell.Value, Chr(160), " "))
I = I + 1
Application.StatusBar = Int(I / CellCount * 100 + 0.5) & "% Trimmed"
Next Cell
Application.Calculation = xlAutomatic
Application.StatusBar = False
Application.DisplayStatusBar = SBar
Application.ScreenUpdating = True
MsgBox "Finished trimming " & CellCount & " cells.", 64
End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question