[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Trim Function/ Excel 2007 VBA

Posted on 2011-02-24
Medium Priority
909 Views
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.

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
Question by:armyssg
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 6

Accepted Solution

FernandoFernandes earned 1000 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
``````
0

LVL 24

Assisted Solution

Tracy earned 1000 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
``````
0

LVL 22

Expert Comment

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

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.

0

LVL 6

Expert Comment

ID: 35210147
:)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.