Trailing spaces~delete

[Brenda]

How can I easily delete all trailing spaces?

TIA,
Brenda

 

[Ken Wright]

By winging your way on over to Dave McRitchies excellent addin called TrimAll. Just like Ronseal,
it does what it says on the tin (The Brits will understand anyway). Available free from:-

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

This will easily take care of the whole worksheet for you in one fell swoop.

 

[Paul Simon]

Hi Brenda,

First select the area to be trimmed, then run this code:

Application.Screenupdating = False
For Each cel In Selection
cel.Value = Application.WorksheetFunction.Trim(cel.Value)
Next
Application.Screenupdating = True


I hope this helps,
Paul

 

[David McRitchie]

Hi Brenda, and Paul,
'You could be in for a long wait (like about 3 minutes per column)
if you tried to select entire columns or the entire sheet.

You might also be in for a very unpleasant
surprise if you had formulas on the page. Take a look at the
TRIMALL macro on my page.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

instructions to install a macro are on my page
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You might also look at some timings on such things in
http://www.mvps.org/dmcritchie/excel/proper#speed

If you know you need a macro in the future the programming
group would be the correct group. If you don't know if you need
a programming solution or a worksheet solution or doesn't fit
you can post the misc group for general type questions.

Since there is no built-in solution for Excel to process the entire
sheet you would need a macro.

 

[Paul Simon]

Thank you very much for your response, David. Your TrimAll code does
indeed work SIGNIFICANTLY faster than the one I suggested (and which I
have been using). The other issues it addresses are important to note
as well. Consequently, I've replaced my code with yours in my
Personal.xls.

Many thanks for this and other very useful code you've shared with the
group.
Paul

Hi Brenda, and Paul,
'You could be in for a long wait (like about 3 minutes per column)
if you tried to select entire columns or the entire sheet.

You might also be in for a very unpleasant
surprise if you had formulas on the page. Take a look at the
TRIMALL macro on my page.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

instructions to install a macro are on my page
http://www.mvps.org/dmcritchie/excel/getstarted.htm

You might also look at some timings on such things in
http://www.mvps.org/dmcritchie/excel/proper#speed

If you know you need a macro in the future the programming
group would be the correct group. If you don't know if you need
a programming solution or a worksheet solution or doesn't fit
you can post the misc group for general type questions.

Since there is no built-in solution for Excel to process the entire
sheet you would need a macro.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi Brenda,

First select the area to be trimmed, then run this code:

Application.Screenupdating = False
For Each cel In Selection
cel.Value = Application.WorksheetFunction.Trim(cel.Value)
Next
Application.Screenupdating = True


I hope this helps,
Paul