add Trim to VBA code within a macro

[heathball]

I have several VBA macros which i hopeful of adding only a 'delete leading and trailing spaces' to.

i apply the macro to different size files, with different row and column lengths.

What i'm after is a code that will apply specifically a 'delete leading and trailing spaces' to what i would normally use which is ActiveSheet.Range("A:BW")
(in this case A:BW is a max range that all the files i have fit within)

I have tried adding TRIM in many ways, following examples without success.

is it possible to use the TRIM function within a VB code to select all the non-empty columns within the worksheet and apply a 'delete leading and trailing spaces' ?
- so that the columns are unchanged after its applied.

Or to apply it to the column range A:BW

which i can simply add to the macros i am using.

Interestingly, the TRIM function appears work by transferring the contents of the cell to another cell, in which the TRIM formula is placed ?
It seems like it is not applied in an efficient way, in comparison to the ASAP add-in 'delete leading and trailing spaces' function, where a range is selected, and the spaces are simply removed?

My next step will be attempting to add the ASAP function into the VBA macro.

Thanks in advance

 

[MARK858]

If your cells are all constants and not formulas try....

Sub TrimABW()
  Dim Addr As String
  Addr = "A1:BW" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate("IF(" & Addr & "="""","""",TRIM(" & Addr & "))")
End Sub

 

[heathball]

Thanks Mark, its astonishing how quickly that works!

It changes some of the columns that contain fractions to dates, as they are formatted as 'general'.

i changed the formatting to 'text' and the code works fantastic.

Thanks very much !

 

[MARK858]

You're welcome (you can probably thank Rick Rothstein for the speed as I suspect I scrounged the idea from him).