Solved

Macro to Use the trim function in Excel 2002

Posted on 2007-11-18
7
1,379 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

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…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
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…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

813 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

10 Experts available now in Live!

Get 1:1 Help Now