TRIM Formula

[chef]

Hi,

My dept takes an electronic download of staff details( from Business Object) and into Excel in columns A to BG under various headings.
we have a few lookups but we need to manually apply the =trim formula in quite a few columns to get rid of trailing spaces in our download

if there a way of applying =trim to each of the columns automatically with a macro so we are left with no trailing spaces in any of the columns.

hope this makes sense and any help is greatly appreciated

regards
ian

 

[VoG]

Try selecting the range then running the TrimAll macro http://dmcritchie.mvps.org/excel/join.htm

 

[kgkev]

Sub trimall()
Dim rng As Range
Dim item As Range
Set rng = Range("A1", Range("BG56000").End(xlUp))
For Each item In rng
    item.Value = Trim(item.Value)
Next item
End Sub

Add this to a module and run when needed.

 

[Misca]

Here's my solution using help range 10 columns right of the range to be trimmed. Change the help range location if needed.:

Sub TrimTheRange()
Dim Rng As Range
Application.ScreenUpdating = False
    Set Rng = ActiveSheet.UsedRange    'Set the range to trim here!
With Rng
        .Offset(0, .Columns.Count + 10).Formula = "=trim(A1)" 'Trims the Rng values using help range 10 columns to the rigth of rng
        .Value = .Offset(0, .Columns.Count + 10).Value 'Replaces the original values with trimmed values
        .Offset(0, .Columns.Count + 10).Clear 'Clears the help range
End With
End Sub

 

[chef]

Hi,

many thanks for all the help....will save a lot of time

regards