VBA to delete leading space
 

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.

VBA to delete leading space
 

On Jan 17, 10:54 am, CB wrote:
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

VBA to delete leading space
 

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)



"CB" wrote in message
...
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.

VBA to delete leading space
 

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

On Jan 17, 10:54 am, CB wrote:
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.

VBA to delete leading space
 

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

"Chip Pearson" wrote:

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)



"CB" wrote in message
...
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.