Trailing Spaces in Merged Cells
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. |
Trailing Spaces in Merged Cells
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" wrote in message ... 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. |
All times are GMT +1. The time now is 12:58 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com