Remove leading, trailing & non-breaking spaces on a worksheet

Hi

I'm an Excel 2010 user and I regularly receive worksheet data that contains leading, trailing & non-breaking spaces.

I remove them by using the worksheet functions =TRIM(CLEAN(SUBSTITUTE(B1,CHAR(160)," ")))

The field that normally contains the troublesome spaces doesn't always appear in column B and because I get them regulary I was hoping someone could enlighten as to how I could create this as a macro?

Many thanks in advance.

Kenneth

Try,

Please Login or Register  to view this content.

Hi AB33

Many thanks, after a few tests, that looks as if it worked a treat!
In the interest of learning can you give a brief explanation of how it works - i understand char 160 but not much else?

Once again, many thanks.

Kenneth

Looping through used range and replacing each cell which has chr 160 with nil string.
You do not have to worry about which column the code refers to. It goes through each used range, but there is a price to pay-Speed will be slow as the loop has to go though the used range.

There may be a short cut and faster code which probably does not require looping.