add Trim to VBA code within a macro

heathball

Board Regular
Joined
Apr 6, 2017
Messages
112
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
If your cells are all constants and not formulas try....

Code:
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
 
Last edited:
Upvote 0
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 !
 
Upvote 0
You're welcome (you can probably thank Rick Rothstein for the speed as I suspect I scrounged the idea from him).
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top