VBA query for Trim

[RAMU]

Dear Friends,

I have a workbook with 4 sheets. Each sheet contains more than one lakh rows & minimum column From A to BR.

I wish to trim all cells of all sheets at a time. I got a VBA query from web which is not working properly:

Sub RemoveTrailingSpaces()
Dim Col As Range, combo As String
Application.ScreenUpdating = False
For Each Col In ActiveSheet.UsedRange.Columns
combo = Join(WorksheetFunction.Transpose(Col), Chr(1))
Do While InStr(combo, " " & Chr(1))
combo = Replace(combo, " " & Chr(1), Chr(1))
Loop
Col = WorksheetFunction.Transpose(Split(combo, Chr(1)))
Next
Application.ScreenUpdating = True
End Sub

After a certain row all cells are showing #N/A after running the above mentioned query.
I need help to get it done. Please help
Regards
RAMU

 

[Fluff]

How about

Sub Ramu()
   With ActiveSheet.UsedRange
      .Value = Evaluate("trim(" & .Address & ")")
   End With
End Sub

But with that amount of data it might take awhile

 

[RAMU]

How about

Sub Ramu()
   With ActiveSheet.UsedRange
      .Value = Evaluate("trim(" & .Address & ")")
   End With
End Sub

But with that amount of data it might take awhile

Dear Sir,
First of all can't get Sub Ramu()
Second I wish to trim all the cells of all sheets at a time. Will it work ?
Regards
RAMU

 

[Fluff]

Please explain what you mean by "First of all can't get Sub Ramu() "

 

[RAMU]

Please explain what you mean by "First of all can't get Sub Ramu() "

Should I write Sub Ramu ( )
I asked that only ? I don't have any knowledge in VBA except copy paste

 

[Fluff]

Just copy the code using the copy button at the top right of the code window

and then paste it into a standard module

 

[RAMU]

Just copy the code using the copy button at the top right of the code window
View attachment 8080
and then paste it into a standard module

Sorry sir, nothing is being copied. I tried but failed

 

[Fluff]

If you can't copy/paste it, then you will need to type it out. Just make sure you do it exactly as written.

 

[RAMU]

If you can't copy/paste it, then you will need to type it out. Just make sure you do it exactly as written.

Ohhh !!! I didn't get you earlier. Now it works fine. Thanks a lot.

 

[Fluff]

You're welcome & thanks for the feedback