Trim all Cells in a Sheet

[tonycm1]

The data that comes out of our corporate database always adds extra spaces to the cells so I wanted a macro that cleans the sheet up for me.

After searching the interwebs, I've managed to find this:

Sub test()
Dim Cel As Range, Rng As Range

Set Rng = ActiveSheet.UsedRange

For Each Cel In Rng '' Causes Run-Time Error
    If Len(Cel) > 0 Then Cel = Trim(Cel)
Next
End Sub

This works great although it always gives me a Run-time error '1004': Application-defined or object-defined error whenever one of the cells in the worksheet originally contained data like " =blabla" (i.e. a bunch of leading spaces with an equal sign).

I assume that the error is because after the Trim, excel is trying to do the sum (or other function) and this is causing the error.

Is there a way I can still trim my worksheet even with these occasional cells occuring?

 

[shg]

Try this:

Sub TC()
    With ActiveSheet.UsedRange
        .NumberFormat = "@"
        TrimRange .Cells
    End With
End Sub
 
Sub TrimRange(r As Range)
    Dim rArea       As Range
    Dim sAdr        As String
    For Each rArea In r.Areas
        With rArea
            sAdr = .Address(ReferenceStyle:=Application.ReferenceStyle, _
                            External:=True)
            .Value = Evaluate("if(row(), substitute(" & sAdr & ", char(160), "" ""))")
            .Value = Evaluate("if(row(), trim( " & sAdr & "))")
        End With
    Next rArea
End Sub

 

[tonycm1]

Woohooo! That code worked great for cells that contain text!

Unfortunately, my worksheet has both Numerical cells and Text cells and this macro ended up turning all the Numerical cells into Text.

Is there a way I can still keep my numerical cells as numerical?

 

[shg]

That would require some different code. I'll posrt something later if noone else does in the meanwhile.

If you don't need the = signs, change the first sub to

Sub TC()
    With ActiveSheet.UsedRange
        .Replace "=", ""
        TrimRange .Cells
    End With
End Sub

... and start again from scratch.

 

[tonycm1]

That last section of code worked for me once I change the line to

         .Replace "=", " ' ="

 

[shg]

So -- sorted?

 

[tonycm1]

[SOLVED]: Trim all Cells in a Sheet

Not sure if this is the proper way to flag a thread "solved" or not on here...

 

[shg]

There isn't, just wanted to confirm that everything's laying flat.