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

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Pavan,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.