PDA

View Full Version : Solved: How Do I clear leading spaces from a column?



alu
06-13-2007, 06:47 AM
Hi, I have a column with 10,000 entries. I would to put some VBA code into a macro to delete spaces that occur after the text in the cells.

I tried TRIM but as the name is diffferant in almost every cell it seeemed like a lot of work to code each one...

thanks all!!

lucas
06-13-2007, 06:49 AM
could you post a couple of the entries and what you would like for them to look like after?

alu
06-13-2007, 06:53 AM
Column B has about 10,000 entries as below:
"John Smith " - about 8 blank spaces after each name
"Jane Match "
"Steven Lutger "

after I would like:
"John Smith"
"Jane Match"
"Steven Lutger"

thanks

Bob Phillips
06-13-2007, 06:56 AM
Dim i As Long

For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
Cells(i, "A").Value = Trim$(Cells(i, "A").Value)
Next i

lucas
06-13-2007, 07:01 AM
Hi alu,
give this a try:
Sub TrimXcessSpaces()
'Macro Purpose: To trim all excess spaces out of cells. This
'eliminates issues where users have cleared the cell with a space,
'and elimates all extra spaces at the beginning or end of a string
Dim cl As Variant
'Loop through cells removing excess spaces
For Each cl In Selection
If Len(cl) > Len(WorksheetFunction.Trim(cl)) Then
cl.Value = WorksheetFunction.Trim(cl)
End If
Next cl
End Sub
You will have to select the area you wish to run it on

alu
06-13-2007, 07:04 AM
It Worked thanks a lot!!!