Solved: Clear white space off each column of each sheet of a workbook

[alu]

Morning people,

I have this code which when I select an enitre sheet will clear white space after text in column A; e.g "Yorrick " becomes "Yorrick".

What I am trying to do is set it so it will clear white space off each column of each sheet of the work book.

Have tried doing with the macro recorder but I am going round in circles :dunno

Sub trimWhiteSpace()
Dim i As Long

For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
Cells(i, "A").Value = Trim$(Cells(i, "A").Value)
Next i

End Sub

Thanks a lot in advance!!

[Bob Phillips]

Sub trimWhiteSpace() Dim i As Long Dim sh As Worksheet Dim j As Long For Each sh In ActiveWorkbook For j = 1 To sh.Cells(1, sh.Columns.Count).End(xlToLeft).Column For i = 1 To sh.Cells(sh.Rows.Count, j).End(xlUp).Row sh.Cells(i, j).Value = Trim$(sh.Cells(i, j).Value) Next i Next j Next sh End Sub

[alu]

Thanks for the quick response XLD,

I ran your code

Sub trimWhiteSpace()

Dim i As Long
Dim sh As Worksheet
Dim j As Long
For Each sh In ActiveWorkbook //Run time Error 438 Object doesn't support this property or method//
For j = 1 To sh.Cells(1, sh.Columns.Count).End(xlToLeft).Column
For i = 1 To sh.Cells(sh.Rows.Count, j).End(xlUp).Row
sh.Cells(i, j).Value = Trim$(sh.Cells(i, j).Value)
Next i
Next j
Next sh
End Sub

But its throwing up an error on line 6, any ideas?

[Bob Phillips]

Sorry, that line should be

For Each sh In ActiveWorkbook.Worksheets

Having problems with VBAX posting, very odd.

[alu]

Thanks xld! it worked a treat first time :thumb