Do it in place - alter the cells themselves?

Sub Remove2LeftBlanks()
Dim cell as Range, rng as Range
Set rng = Range(cells(1,1),cells(rows.count).End(xlup))
for each cell in rng
if Left(cell.Value = " ") then
cell.Value = Mid(cell.Value,3,len(cell)-2)
end if
Next
End Sub

to put the results in the cell to the right:

Sub ReturnAllbut2LeftBlanks()
Dim cell as Range, rng as Range
Set rng = Range(cells(1,1),cells(rows.count).End(xlup))
for each cell in rng
if Left(cell.Value = " ") then
cell.Offset(0,1).Value = Mid(cell.Value,3,len(cell)-2)
end if
Next
End Sub

--
Regards,
Tom Ogilvy



"Ricky Pang" wrote in message
...
Hi Tom,
I have a similar question about spaces before the text.
=IF(ISBLANK(A1),"",IF(LEFT(A1,2)=" ",MID(A1,3,LEN(A1)),A1))

I'm using this formula to remove just the first 2 spaces only within the
text. How do I convert this to code? I've tried the Replace function
but it doesn't work right. When I enter just 2 spaces, it deletes all
spaces prior to the text. And I wanted to keep the remaining spaces
after the first 2 spaces.

I found this previously posted solution from Mr. Benson. I also like
your code which includes all of column A:

Sub RemoveSpaces()
Dim Rng As Range, Cell As Range
Set Rng = ActiveSheet.UsedRange
For Each Cell In Rng
If Not IsEmpty(Cell) _
And Len(Cell) = 1 _
And Right(Cell, 1) = Chr(32) _
And Not Cell.HasFormula Then _
Cell = RTrim(Cell)
Next
End Sub

How do you convert this to just remove the first 2 spaces from the left?
If the cell is empty, ignore and move down the list (all within column
A). Do I have to factor in the Chr(160) also?

Thanks so much,
Ricky

*** Sent via Developersdex http://www.developersdex.com ***