VBA help please - remove leading spaces

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

Originally Posted by 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

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

try using the following;

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

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

--
Don Guillett
SalesAid Software
[email protected]
"Scott Wagner" <[email protected]> wrote in message
news:[email protected]...
> 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

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




Scott Wagner wrote:
>
> 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

--

Dave Peterson

Thanks to all for responding!



"Dave Peterson" wrote:

> dim myCell as range
> for each mycell in selection.cells
> mycell.formula = ltrim(mycell.formula)
> next mycell
>
>
>
>
> Scott Wagner wrote:
> >
> > 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
>
> --
>
> Dave Peterson
>

what worked?

--
Don Guillett
SalesAid Software
[email protected]
"Scott Wagner" <[email protected]> wrote in message
news:[email protected]...
> Thanks to all for responding!
>
>
>
> "Dave Peterson" wrote:
>
>> dim myCell as range
>> for each mycell in selection.cells
>> mycell.formula = ltrim(mycell.formula)
>> next mycell
>>
>>
>>
>>
>> Scott Wagner wrote:
>> >
>> > 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
>>
>> --
>>
>> Dave Peterson
>>