• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 897
  • Last Modified:

VBA Macro to trim all rows/columns in excel 2007

Related to:  http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_24457092.html?sfQueryTermInfo=1+10+all+column+row+trim

Need to understand how to create code in Excel 2007, put it on a button on the tool bar and give it to someone else to use whenever they open up a certain CSV file.

The link above looks like it might work but not sure how to use and accomplish what I listed above.
Any help would be appreciated.
Thanks, Pat
0
PatKung
Asked:
PatKung
  • 7
  • 2
  • 2
  • +2
1 Solution
 
Saurabh Singh TeotiaCommented:
You can either record the enitre steps of macro what patrick said, To record a macro in excel 2007, go to the devloper ribbon and record the macro and in case if you are not able to see the devloper ribbon, follow these steps to get it.
http://office.microsoft.com/en-us/excel/HA101730521033.aspx
Or alternatively you can use the following code...However the replace method will be faster then the below code.
Saurabh...

Sub trimmydata()
Dim rng As Range, cell As Range

Set rng = UsedRange

For Each cell In rng
cell.Value = Trim(cell.Value)
Next cell

End Sub

Open in new window

0
 
PatKungAuthor Commented:
But I do not want to replace all spaces, just do a trim.  So if I understand how a trim works, it would remove any leading, trailing and extra spaces in the middle.  But there would remain one space between each word.  Is that correct?  
0
 
Rory ArchibaldCommented:
Not if you use the VBA trim function, no - it won't do extra spaces in the middle. You need:

Sub trimmydata()
Dim rng As Range, cell As Range

Set rng = UsedRange

For Each cell In rng
cell.Value = Application.WorksheetFunction.Trim(cell.Value)
Next cell

End Sub

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
PatKungAuthor Commented:
OK, now that the code is right, how do I accomplish using it in an Excel sheet.  Have never done this before. Need to put it on a button on the tool bar and give it to someone else to use whenever they open up a certain CSV file.

Thanks, Pat
0
 
aspeharCommented:
I use this: http://www.ablebits.com/excel-trim-spaces/index.php
It automatically adds a new tab with button to trim spaces.
0
 
Rory ArchibaldCommented:
See this site for example as to how to do it with the Quick Access Toolbar: http://www.rondebruin.nl/qat2.htm
 Doing it for the Ribbon is much harder unless you can have it just appear in an Addins tab on the right hand side of the ribbon.
 
0
 
PatKungAuthor Commented:
Will this add in also take care of the imbedded extra spaces?  So if I had a cell that contained:
"This has too    many spaces in the middle."
Would it remove all except one between each word?
Thanks, Pat
0
 
aspeharCommented:
AbleBits is only leading and trailing spaces. I misunderstood what you were looking for. If you create a macro that replaces spaces as saurabh726 suggested, you could do more than one find and replace.  For example:
Record Macro
Replace {space} with {}
Replace {space}{space} with {}
Replace {space}{space}{space} with {}
etc... where {} is nothing.
Stop recording.
Then you can add the macro to the QAT as suggested by rorya.
0
 
PatKungAuthor Commented:
As I stated earlier, I do not want to remove ALL spaces only the extra spaces.  So example:
"   This has too    many spaces in the middle.   " 
Would become:
"This has too many spaces in the middle."
So it looks like I need to use the code from  rorya.
Thanks, Pat
0
 
PatKungAuthor Commented:
I will look at this more tomorrow.  Gone today.  Thanks, Pat
0
 
sekrinCommented:
Note that I haven't tested this, but if you replace the following line in rorya's code above:
cell.Value = Trim(cell.Value)

with
cell.Value = join(split(trim(cell.Value), " "), " ")

I believe that should do what you need.
0
 
PatKungAuthor Commented:
Have not abandoned question, just got too busy at work for a few days.  Will try to look at this again on Monday.
Thanks, Pat
0
 
PatKungAuthor Commented:
sekrin,
I put your code in and replaced the line you mentioned but it still does not deal with extra spaces in the middle of text in the cell.
Could you explain what the line:  cell.Value = join(split(trim(cell.Value), " "), " ")
is supposed to be doing?

rorya,
Your code worked great, after I figured out how to make it run.
Thanks, Pat

Thanks, Pat
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now