asked on

VBA Macro to trim all rows/columns in excel 2003

Hi all,

Looking for a simple VBA Macro that I can put into Excel 2003 and have it auto trim all columns and or cells of the current worksheet in the current file that is open. I should be able to press a macro key and have it auto trim everything and remove all white spaces. This macro doesnt need to save the workbook. Just so I know it did perform the task I would like it to pop up a message on the screen to say it trimed all items then click on to close the message box.

Thank you!

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

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

Start Free Trial

nutsch

Try this macro. Do you know how to add a macro button to the sheet?

Thomas

Sub TrimAllSheet()
Dim cl As range, rng As range
 
Set rng = range(range("A1"), range("A1").SpecialCells(xlLastCell))
 
For Each cl In rng.Cells
 
    cl = Trim(cl)
 
Next
 
MsgBox "All items have been trimmed"
 
End Sub

Open in new window

nutsch

and of course, Patrick's answer is accurate.

Thomas

smyers051972

ASKER

Thanks and heres some easy points :)

smyers051972

ASKER

Doh
Thanks for the reply. I was accepting as you wrote that too!

We can request attention though if you feel you should of earned some of the points. Opinions?

nutsch

Patrick's answered the question well enough for your needs. He can keep the points, it's fine by me.

My contribution to the 2,077,895 points he needs...

T

Patrick Matthews

smyers051972,

It's up to you. I have no objection to a split.

Thomas,

I've got a lot longer to go than that for the next cert in Excel. Of course, a few months ago EE sent me a
Savant shirt when they were supposed to send a Sage, so I already have the shirt :)

Regards,

Patrick

nutsch

That's the usual t-shirt swap, I got the savant for the sage too last year. I don't dare wear it though, just in case I'd meet Kevin in the street.

And the 2M points is for your general EE savant rank, not Excel cert yet. I don't want to put too much pressure on you.

T

Patrick Matthews

nutsch said:
>>That's the usual t-shirt swap, I got the savant for the sage too last year.

And here I thought I was special :)

>>I don't dare wear it though, just in case I'd meet Kevin in the street.

I wouldn't worry. Kevin's seen me wearing mine, and I heard nary a peep.

>>And the 2M points is for your general EE savant rank, not Excel cert yet. I don't want to put too much pressure on you.

LOL!