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
  • Learn & ask questions
Solved

Macro to Use the trim function in Excel 2002

Posted on 2007-11-18
7
1,382 Views
Last Modified: 2008-02-01
I need to use the Trim function to clean text in a long column [my range is named f250]
I have searched the knowledgebase and see this question was answered earlier this year, but I cannot understand the answer.  There were in fact two similar answers, as follows
Public Sub CleanWhitey()
Dim x As Range
Application.ScreenUpdating = False
For Each x In UsedRange
    x = Trim(x)
Next x
Application.ScreenUpdating = True
End Sub

Sub cleaner()
    UsedRange = Trim(UsedRange)
End Sub
I don't understand how to apply these answers to my spreadsheet.  What is "UsedRange" which both answers use, and what is "x" in the first?
Thanks for any guidance
0
Comment
Question by:aws148
  • 4
  • 2
7 Comments
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20307183
Usedrange is representing all cells which are used in the active sheet.

the x is the variable where the value is stored.

In this case when: For Each x In UsedRange
is mentioned then it goes through all cells in the usedrange. and stores that value in in x.
This is then used in the trim function.
x = Trim(x)

means that the trimmed value is stored back to the location where it was found.
I hope this gives you some help

regards,
Jeroen
0
 
LVL 33

Accepted Solution

by:
Jeroen Rosink earned 350 total points
ID: 20307193
To use the trim function in a certain column you might also do something like this:
here another example:
Public Sub CleanWhitey()
yourcol = 6 'enter number of your column
cntRows = Cells(Rows.Count, yourcol).End(xlUp).Row
Application.ScreenUpdating = False
For i = 1 To cntRows
    If Cells(i, yourcol) <> "" Then
        Cells(i, yourcol) = Trim(Cells(i, yourcol))
    End If
Next i
Application.ScreenUpdating = True
End Sub

it cleans the values in your column
0
 

Author Comment

by:aws148
ID: 20307389
Thankyou.  Your code to use the function on a specific columne works well.

But my range is in fact several columns, and I was after code to trim all cells in the named range.  Is that possible?

Is UsedRange an Excel defined word?   I could not get the two macros in my question to work and don't understand why.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 33

Assisted Solution

by:Jeroen Rosink
Jeroen Rosink earned 350 total points
ID: 20307508
UsedRange is as far as I know spefic for Excel VBA.

here an alternative which trims only the cells you selected:
Public Sub CleanWhitey()
Application.ScreenUpdating = False
For Each cel In Selection
    If cel <> "" Then
        cel.Value = Trim(cel)
    End If
Next cel
Application.ScreenUpdating = True
End Sub

0
 
LVL 38

Assisted Solution

by:jeverist
jeverist earned 150 total points
ID: 20307645
Hi aws148,

>   I could not get the two macros in my question to work and don't understand why.

'UsedRange' needs the worksheet object.  The correct syntax for the two routines would be:

Public Sub CleanWhitey()
Dim x As Range
Application.ScreenUpdating = False
For Each x In ActiveSheet.UsedRange
    x = Trim(x)
Next x
Application.ScreenUpdating = True
End Sub

Sub cleaner()
    ActiveSheet.UsedRange = Trim(ActiveSheet.UsedRange)
End Sub

The second one (cleaner()) won't work, though.  You can't trim the entire range at once like that.

Jim
0
 

Author Comment

by:aws148
ID: 20307778
Thanks very much to both of you.  All answers have helped.
Don't really know how to split the points.  I've decided 350 - 150 in view of speed and number of roos01 helpful responses, as against the helpful explanation from jeverist.  Hope that is acceptable.
Thanks again
0
 
LVL 33

Expert Comment

by:Jeroen Rosink
ID: 20307788
To split up the points you might take a look here:
http://www.experts-exchange.com/help.jsp#hi69

here is stated to select the button: "Accept Multiple Solutions"

Jeroen
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

828 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