VBA to delete leading space

[CB]

I have a column of data that has a leading blank in each cell. Does someone
have a code example that I can use to delete this leading space? I have
spaces between data in these cells and just need to delete the leading space
on the left hand side of each cell. Any and all replies are greatly
appreciated.

 

[SteveM]

I have a column of data that has a leading blank in each cell. Does someone
have a code example that I can use to delete this leading space? I have
spaces between data in these cells and just need to delete the leading space
on the left hand side of each cell. Any and all replies are greatly
appreciated.

In an adjacent column use this formula

=Right("RefCell', Len("RefCell") - 1)


That should lop off the leading blank.

SteveM

 

[Chip Pearson]

The LTrim function removes spaces on the left of a String. For example,

Sub RemoveLeadingSpace()
Dim R As Range
Application.EnableEvents = False
On Error GoTo ErrH:
If TypeOf Selection Is Excel.Range Then
For Each R In Selection.Cells
If R.HasFormula = False Then
R.Value = LTrim(R.Value)
End If
Next R
End If
ErrH:
Application.EnableEvents = True
End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Jason Lepack]

Is this a one time fix or something to be done repeatedly?

One time:

place formula in cell and fill down.

=RIGHT(A1, LEN(A1)-1)


If this is going in a macro then it will work quite similar.

Public Sub remove_space()
Dim c As Range ' Cell
For Each c In ActiveSheet.Range("A:A")
If Len(c) > 0 Then
c = Mid(c, 2)
End If
Next c
End Sub

Cheers,
Jason Lepack

 

[CB]

Chip,
Thanks so much. This is exactly what I needed. You have saved me much time
and many key strokes.
cb