Remove zeros

I have a list that looks like this:

0
1250897
0
0
9850254

I need to remove all the single zeros but not those within a larger number.
And I need to write this into a macro. Thanks for any suggestions.

Can't you just sort and delete the rows? Record a macro if desired.

--
Don Guillett
SalesAid Software
donaldb@281.com
"xgirl" <xgirl@discussions.microsoft.com> wrote in message
news:B2F20246-039D-47D5-BAB8-B75B9AD2737C@microsoft.com...
> I have a list that looks like this:
>
> 0
> 1250897
> 0
> 0
> 9850254
>
> I need to remove all the single zeros but not those within a larger
number.
> And I need to write this into a macro. Thanks for any suggestions.

No, I can't there is text inter-mixed with the data on different rows. I
just want to get rid of the zeros in the one column.

"Don Guillett" wrote:

> Can't you just sort and delete the rows? Record a macro if desired.
>
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "xgirl" <xgirl@discussions.microsoft.com> wrote in message
> news:B2F20246-039D-47D5-BAB8-B75B9AD2737C@microsoft.com...
> > I have a list that looks like this:
> >
> > 0
> > 1250897
> > 0
> > 0
> > 9850254
> >
> > I need to remove all the single zeros but not those within a larger
> number.
> > And I need to write this into a macro. Thanks for any suggestions.
>
>
>

Select the column.

Data>Filter>Autofilter.

Click on drop arrow and select 0 from the choices.

Edit>Clear Contents.

Data>Filter>Autofilter to turn off.


Gord Dibben Excel MVP

On Mon, 11 Apr 2005 17:07:02 -0700, "xgirl" <xgirl@discussions.microsoft.com>
wrote:

>No, I can't there is text inter-mixed with the data on different rows. I
>just want to get rid of the zeros in the one column.
>
>"Don Guillett" wrote:
>
>> Can't you just sort and delete the rows? Record a macro if desired.
>>
>> --
>> Don Guillett
>> SalesAid Software
>> donaldb@281.com
>> "xgirl" <xgirl@discussions.microsoft.com> wrote in message
>> news:B2F20246-039D-47D5-BAB8-B75B9AD2737C@microsoft.com...
>> > I have a list that looks like this:
>> >
>> > 0
>> > 1250897
>> > 0
>> > 0
>> > 9850254
>> >
>> > I need to remove all the single zeros but not those within a larger
>> number.
>> > And I need to write this into a macro. Thanks for any suggestions.
>>
>>
>>

Put this in B1 and copy down...........

=IF(A1=0,"",A1)

Vaya con Dios,
Chuck, CABGx3


"xgirl" <xgirl@discussions.microsoft.com> wrote in message
news:B2F20246-039D-47D5-BAB8-B75B9AD2737C@microsoft.com...
> I have a list that looks like this:
>
> 0
> 1250897
> 0
> 0
> 9850254
>
> I need to remove all the single zeros but not those within a larger
number.
> And I need to write this into a macro. Thanks for any suggestions.

Thanks Chuck, that will work.

"CLR" wrote:

> Put this in B1 and copy down...........
>
> =IF(A1=0,"",A1)
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "xgirl" <xgirl@discussions.microsoft.com> wrote in message
> news:B2F20246-039D-47D5-BAB8-B75B9AD2737C@microsoft.com...
> > I have a list that looks like this:
> >
> > 0
> > 1250897
> > 0
> > 0
> > 9850254
> >
> > I need to remove all the single zeros but not those within a larger
> number.
> > And I need to write this into a macro. Thanks for any suggestions.
>
>
>

You're welcome..........glad to help


Vaya con Dios,
Chuck, CABGx3


"xgirl" <xgirl@discussions.microsoft.com> wrote in message
news:85CC1E4B-0B69-4FA6-A1E4-BAC1158BC35B@microsoft.com...
> Thanks Chuck, that will work.
>
> "CLR" wrote:
>
> > Put this in B1 and copy down...........
> >
> > =IF(A1=0,"",A1)
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> > "xgirl" <xgirl@discussions.microsoft.com> wrote in message
> > news:B2F20246-039D-47D5-BAB8-B75B9AD2737C@microsoft.com...
> > > I have a list that looks like this:
> > >
> > > 0
> > > 1250897
> > > 0
> > > 0
> > > 9850254
> > >
> > > I need to remove all the single zeros but not those within a larger
> > number.
> > > And I need to write this into a macro. Thanks for any suggestions.
> >
> >
> >

Highlight your list of numbers, then press Ctrl-H

In the dialog, search for 0 and replace it with nothing. Click on the
Options button and check the option for Match Entire Cell Contents

Record it to a macro if needs be, but be sure to adjust the range it selects
before putting it into production

"xgirl" wrote:

> I have a list that looks like this:
>
> 0
> 1250897
> 0
> 0
> 9850254
>
> I need to remove all the single zeros but not those within a larger number.
> And I need to write this into a macro. Thanks for any suggestions.

Hi

how about
---
Sub clearzeros()
For Each c In Selection
If c.Value = 0 Then
c.ClearContents
End If
Next
End Sub
---

to use, right mouse click on a sheet tab, choose view code, in the VBE
window choose insert / module, copy & paste the above code there, use ALT &
F11 to switch back to your workbook ... select the range you want to do this
to and choose tools / macro / macros .. .find clearzeros and press run.

NOTE: it is always a good idea to try things like this on a copy of your
workbook first just in case

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"xgirl" <xgirl@discussions.microsoft.com> wrote in message
news:B2F20246-039D-47D5-BAB8-B75B9AD2737C@microsoft.com...
>I have a list that looks like this:
>
> 0
> 1250897
> 0
> 0
> 9850254
>
> I need to remove all the single zeros but not those within a larger
> number.
> And I need to write this into a macro. Thanks for any suggestions.