Sub Remove2LeftBlanks()
Dim cell As Range, rng As Range
on Error Resume Next
set rng = Activesheet.Names("ABC").ReferstoRange
On error resume next
if rng is nothing then
Set rng = Range(Cells(1,1),Cells(rows.count,1).End(xlup))
For Each cell In rng
If Left(cell.Value, 2) = " " Then
cell.Value = Mid(cell.Value, 3, Len(cell) - 2)
End If
Next
' create the named range "ABC" to
' indicate the macro has been run.
rng.name = "'" & activesheet.Name & "'!ABC"
else
Msgbox "Already been run"
End if
End Sub

--
Regards,
Tom Ogilvy

"Ricky Pang" wrote in message
...
Hi Tom,
It worked! I made a slight change to the "Set rng" line so that the
code would run through the entire column. I hope this right (it does
work) but is this how you would write the change?
As importantly, I'd like to put an If..then.. checker on the page so
that if this code has already been activated once already, it would stop
executing to prevent the deletions of the remaining spaces that I do
want to keep.

Something to the effect of; the last action of this code is to put a
word in cell "D1" such as "Spaces already deleted".
Then, if anyone runs it again by accident, the first thing that the code
does is to check if "D1" has the word "Spaces already deleted"
indicated. If it does, then code stops. If not, then code will delete
the empty spaces.

Sub Remove2LeftBlanks()
Dim cell As Range, rng As Range
Set rng = Range("A:A")
For Each cell In rng
If Left(cell.Value, 2) = " " Then
cell.Value = Mid(cell.Value, 3, Len(cell) - 2)
End If
Next
End Sub

Your assistance is greatly appreciated.

Thanks again,
Ricky

P.S. I'm trying to go through your Msgbox Search and Replace code.
Something is not quite working. I'll get back to you very shortly.


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