I need a macro that can delete zero's from a range of cells-the numbers all
start with 0 for example-000222, next cell 000228.
I want to delete the zeros but leave the rest of the number. when I write a
macro, it replaces the number with a constant rather than leaving the new
number in each cell-
can anyone help?

Thanks, Kathy Beck

Either the cells are formatted as text or have a custom format applied.

First I'd try to select the cells (or entire column) and then use
Edit | Clear | Formats
and see if that clears it up.

If not, again choose them and use
Format | Cells | [General]


"Kathy Marie" wrote:

I need a macro that can delete zero's from a range of cells-the numbers all
start with 0 for example-000222, next cell 000228.
I want to delete the zeros but leave the rest of the number. when I write a
macro, it replaces the number with a constant rather than leaving the new
number in each cell-
can anyone help?

Thanks, Kathy Beck

Enter 1 in a spare cell.

Copy the cell, select the desire range and Paste Special|Multiply.

Lot's of great solutions:


1. select the cells and pull-down:
Edit Replace put 000 in the find what and leave the replace empty

2. select the cells and pull-down:
Edit Replace and put 0 in the find what and leave the replace empty
this will also remove internal zeros

3. the macro solution:
Sub zerokiller()
Dim s As String
For Each r In Selection
s = r.Value
s = Application.WorksheetFunction.Substitute(s, "0", "")
r.Value = s
Next
End Sub


--
Gary''s Student


"Kathy Marie" wrote:

I need a macro that can delete zero's from a range of cells-the numbers all
start with 0 for example-000222, next cell 000228.
I want to delete the zeros but leave the rest of the number. when I write a
macro, it replaces the number with a constant rather than leaving the new
number in each cell-
can anyone help?

Thanks, Kathy Beck

Thanks so much,

This worked and was simple

"JLatham" wrote:

Either the cells are formatted as text or have a custom format applied.

First I'd try to select the cells (or entire column) and then use
Edit | Clear | Formats
and see if that clears it up.

If not, again choose them and use
Format | Cells | [General]


"Kathy Marie" wrote:

I need a macro that can delete zero's from a range of cells-the numbers all
start with 0 for example-000222, next cell 000228.
I want to delete the zeros but leave the rest of the number. when I write a
macro, it replaces the number with a constant rather than leaving the new
number in each cell-
can anyone help?

Thanks, Kathy Beck