asked on

Trim Function/ Excel 2007 VBA

Experts- Please assist with the following.

Request: I need a VBA (Trim Function) Code that can loop through five Excel 2007 worksheets while removing all leading/trailing spaces.

Additional Info
a) My dataset range spans from Column A through Column M with a variable number of rows

b) These spaces are created by pasting data from a web page.

c) There are no spaces between text in cells.

Thanks,

armySSG

ASKER CERTIFIED SOLUTION

FernandoFernandes

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Tracy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rspahitz

You could also use the built-in Excel functions for that.

Go to a new sheet and in cell A1 put a formula like this:

=Trim(Sheet1!A1)

Copy that to all cells that are in the range of the original sheet's data and you're ready to go.
If you want this to be saved without the spaces, select all cells on the new sheet and Paste-Special-Values back onto itself and you're done.

armyssg

ASKER

Both answers seemed to have worked perfect. My thanks to both of your for the quick feedback answer. And my apologies for not awarding the points sooner. Somehow, I thought I did, but I am guessing it didn't register when I tried last month due to network issues we were having at work.

Thanks again for the great answers.

rspahitz- Your answer was good as well, but I was looking for a vba answer specifically.

FernandoFernandes