Solved

Macro to Use the trim function in Excel 2002

Posted on 2007-11-18
7
1,376 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

920 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

11 Experts available now in Live!

Get 1:1 Help Now