VBA - Trim Range Copy/Paste

Hi rafapmotta,
Here is what I think you are trying to do:

Please Login or Register  to view this content.

You may have found a very interesting thing about the TRIM() function when use in Excel vs VBA.

In Excel (in a cell) the TRIM() function removes spaces in front and behind a string AND also replaces double spaces with a single space.

Example: = Trim(" Space In Front and End " ) gives back "Space In Front and End"
While if you use the VBA form of Trim it only trims the leading and trailing spaces and does not compress multiple spaces into a single space.
Example: = Trim(" Space In Front and End " ) gives back "Space In Front and End"

Note: The examples above don't look correct as our web site compresses multiple spaces into single spaces. There are extra spaces between words in the VBA Trim() function when I posted it and/or edit it.

If you are trying to replace multiple spaces to a single space using the Trim function using VBA you will need to use the Excel Worksheet function as shown above.
WorksheetFunction.Trim().

I hope that answers your question as you've stumbled onto differences between VBA and Excel function of TRIM() differences.

Hello rafapmotta,

Maybe you would want to try just a slight adjustment to the code by MarvinP.

Please Login or Register  to view this content.

Regrads.