Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 916
  • Last Modified:

Trim Function/ Excel 2007 VBA

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
armyssg
Asked:
armyssg
2 Solutions
 
FernandoFernandesCommented:
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
 
TracyVBA DeveloperCommented:
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
 
rspahitzCommented:
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
 
armyssgAuthor Commented:
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
 
FernandoFernandesCommented:
:)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now