Thread: Trim all Cells in a sheet

Hi,
I'm new here ans have only basic understanding of VBA. However, once it works, I do love it. Here is my problem.
I'm trying to trim all the data in my worksheet in order to prepare it for several steps of further analysis. Running the below code will cause an error that I don't understand. It says:
Runtime error '1004': Application-defined or object-defined error

the code is (error causing line indicated in comments):

PHP Code: 

Sub trimAll()
    Application.ScreenUpdating = False
    Dim Rows As Long
    Dim Column As Long
    Dim i As Integer
        Rows = ActiveSheet.UsedRange.Rows.Count
        Column = ActiveSheet.UsedRange.Columns.Count
    For i = 1 To Column
            Columns(i).Select
            Selection.Insert Shift:=xlToRight
            ActiveSheet.Range(Cells(1, i), Cells(Number, i)) = "=TRIM(RC[1])"  ** ERROR **
            Columns(i).Select
            Selection.Copy
            Columns(i + 1).Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False
            Columns(i).Select
            Selection.Delete Shift:=xlToLeft
    Next i
    Application.ScreenUpdating = True
End Sub 


Hi,

Try this one

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

Set Rng = ActiveSheet.UsedRange

For Each Cel In Rng
    If Len(Cel) > 0 Then Cel = Trim(Cel)
Next
End Sub

In your code you didn't dim the variable 'Number'. Also try to avoid dim the variables like Rows, Column as these are reserved in VBA.

HTH

You code is entering an R1C1 formula without telling Excel to set the FormulaR1C1 property.

It's also assuming that the used range always starts in row 1, which may not be true.

Here's an option, assuming there are no formulas on the sheet:

Sub x()
    With ActiveSheet.UsedRange
        .Value = Evaluate("if(row(" & .Address & "), trim(" & .Address & "))")
    End With
End Sub

Originally Posted by shg

You code is entering an R1C1 formula without telling Excel to set the FormulaR1C1 property.

It's also assuming that the used range always starts in row 1, which may not be true.

Here's an option, assuming there are no formulas on the sheet:

Sub x()
    With ActiveSheet.UsedRange
        .Value = Evaluate("if(row(" & .Address & "), trim(" & .Address & "))")
    End With
End Sub

Hi

The second code runs fine. The result is different to my first code thouhg.
If I use the formula =trim(whateverCell) , everything will then be in text format and all empty spaces are gone.
Is it possible that the VBA code "trim()" is different to that?

thanks for your time! Much appreciated!!

The VBA Trim and Worksheet TRIM functions are different; TRIM replaces sequential interior spaces with one, Trim does not.

Hi,

No idea why it causes the error. Is it(Sheet) protected?

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save