Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Macro to Use the trim function in Excel 2002

Posted on 2007-11-18
7
Medium Priority
?
1,398 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 1400 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 33

Assisted Solution

by:Jeroen Rosink
Jeroen Rosink earned 1400 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 600 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
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 will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

876 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