Prev: Insert Comment
Next: Text colour keeps changing to grey
From: Meng on 12 Mar 2010 01:14
Hi,

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.
From: macropod on 12 Mar 2010 22:30
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
Else
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

--
Cheers
macropod
[Microsoft MVP - Word]


"Meng" <Meng(a)discussions.microsoft.com> wrote in message news:4375097A-C714-4BFA-8931-24C7803AEECD(a)microsoft.com...
> Hi,
>
> 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.
 | 
Pages: 1
Prev: Insert Comment
Next: Text colour keeps changing to grey