Remove zeros

[Guest]

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.

 

[Don Guillett]

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

 

[Guest]

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.

 

[Gord Dibben]

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

 

[CLR]

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

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

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Thanks Chuck, that will work.

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

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

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

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


Vaya con Dios,
Chuck, CABGx3

 

[Guest]

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

 

[JulieD]

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