Solved

Trim Function/ Excel 2007 VBA

Posted on 2011-02-24
5
851 Views
Last Modified: 2012-05-11
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
 
0
Comment
Question by:armyssg
5 Comments
 
LVL 6

Accepted Solution

by:
FernandoFernandes earned 250 total points
ID: 34973187
Try to use this code
Sub TrimSheets
dim i as integer
dim sht as worksheet
dim rng as range
for i = 1 to thisworkbook.sheets.count
set sht = thisworkbook.sheets(i)
sht.unprotect
for each rng in sht.usedrange.cells
rng.formula = trim(rng.formula)
next rng
set sht = nothing
next i
End Sub

Open in new window

0
 
LVL 24

Assisted Solution

by:broomee9
broomee9 earned 250 total points
ID: 34973199
Try something like this:
Option Explicit

Sub RemoveSpaces()

    Dim wkSht As Worksheet
    Dim cel As Range
    
    For Each wkSht In ThisWorkbook.Worksheets
        For Each cel In wkSht.UsedRange
            cel.Value = Trim(cel.Value)
        Next
    Next wkSht

End Sub

Open in new window

0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34973243
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.
0
 

Author Closing Comment

by:armyssg
ID: 35210093
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.
0
 
LVL 6

Expert Comment

by:FernandoFernandes
ID: 35210147
:)
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now