Solved

Macro to Use the trim function in Excel 2002

Posted on 2007-11-18
7
1,373 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:roos01
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:
roos01 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 33

Assisted Solution

by:roos01
roos01 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:roos01
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

705 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

20 Experts available now in Live!

Get 1:1 Help Now