Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 154
  • Last Modified:

trailing spaces all columns

I have a sheet with loads columns, and I have just noticed, in all the cells of data - all the cell have endless trailing spaces. I had been creating new columns aside of each of the original columns, and using =trim(A2) to get rid of them, but its taking a long time to complete. Can anyone think of a quick way to remove all trailing spaces on all columns in the report in one single go? Or is my current technique the only way possible.
0
pma111
Asked:
pma111
3 Solutions
 
Martin LissOlder than dirtCommented:
Here's a macro you can use.

Sub TrimAll()
Dim cel As Range
For Each cel In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
    cel = WorksheetFunction.Trim(cel)
Next
End Sub

Open in new window

1
 
Glenn RayExcel VBA DeveloperCommented:
I use a third-party application to clean up ranges of data like that (ASAP Utilities).  But, here is a macro that will do the same.  You just highlight the range of data to clean up and run the macro.

Sub ReEnter()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim rng As Range
    Dim c As Object
    Set rng = Selection
    For Each c In rng
        c.Formula = Trim(c.Formula)
    Next c
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Open in new window


-Glenn
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Here is another one which will be faster to finish the task.
Sub RemoveTrailingSpaces()
Dim arr
Dim i As Long, j As Long
On Error Resume Next
arr = ActiveSheet.UsedRange.Value
For i = 1 To UBound(arr, 1)
   For j = 1 To UBound(arr, 2)
      arr(i, j) = Trim(arr(i, j))
   Next j
Next i
Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End Sub

Open in new window

0
 
Rob HensonFinance AnalystCommented:
You could have carried on with the simple =TRIM(A2) method but go about it differently. Sounds like you are doing it one column at a time rather than doing it as a block.

Assuming your data is columns A to Z, in column AA put your =TRIM(A2) formula and copy across to column AZ. In other words just copy to the right until you have all columns of original data covered.

Select cells AA2 to AZ2 (or however far you needed to go right) and hover your cursor over the bottom right corner of AZ2 until it turns to a + sign. Double click and the formulas will be copied down until it reaches the last entry in column Z. You can then copy these new columns and rows and paste values over the original.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now