VBA help please - remove leading spaces

[Scott Wagner]

In column D of my worksheet some of the cell contents have a varying number
of spaces. How can I removed any leading spaces from those cells with VBA?

There are spaces within the cells (not leading) that I want to keep.

Example below.

Here is what I have now:

ColD
This is the text
Blah Blah Blah
Blah Blah Blah Blah
This is the text

Here is what I'd like to end up with:

ColD
This is the text
Blah Blah Blah
Blah Blah Blah Blah
This is the text

Thanks in advance!

Scott

 

[funkymonkUK]

In column D of my worksheet some of the cell contents have a varyin
number
of spaces. How can I removed any leading spaces from those cells wit
VBA?

There are spaces within the cells (not leading) that I want to keep.

Example below.

Here is what I have now:

ColD
This is the text
Blah Blah Blah
Blah Blah Blah Blah
This is the text

Here is what I'd like to end up with:

ColD
This is the text
Blah Blah Blah
Blah Blah Blah Blah
This is the text

Thanks in advance!

Scott

I must either be either rather pissed or need to get my eye sigh
tested but there does not seem to a difference between what you hav
now and what you want

 

[gti_jobert]

try using the following;

Dim i%
do
Cells(i, 4).Value = Trim(Cells(i, 4).Value)
loop until Cells(i, 4).Value = ""

 

[Don Guillett]

try
=SUBSTITUTE(A14,CHAR(160),"")

 

[Dave Peterson]

dim myCell as range
for each mycell in selection.cells
mycell.formula = ltrim(mycell.formula)
next mycell

 

[Scott Wagner]

Thanks to all for responding!

dim myCell as range
for each mycell in selection.cells
mycell.formula = ltrim(mycell.formula)
next mycell

 

[Don Guillett]

what worked?