Replacing 0 value with empty

[danpt]

I need a vba code to replace all the cells having 0 value with empty cell in
a selected range.

Could there be a better way than the following:
Sub zerotoblank()
Dim r, c, rowstart, rowend, colstart, colend As Long
rowstart = Selection.Row
rowend = Selection.Rows.Count + rowstart - 1
colstart = Selection.Column
colend = Selection.Columns.Count + colstart - 1
For c = colstart To colend
For r = rowstart To rowend
If Cells(r, c) = 0 Then Cells(r, c) = ""
Next r
Next c
End Sub

 

[Jim Cone]

This could be faster; it is simpler...
'--
Sub zerotoblank()
Dim rCell As Range
For Each rCell In Selection.Cells
If rCell.Value = 0 Then rCell.Value = vbNullString
Next
End Sub

--
Jim Cone
Portland, Oregon USA




"danpt" <[email protected]>
wrote in message
I need a vba code to replace all the cells having 0 value with empty cell in
a selected range.

Could there be a better way than the following:
Sub zerotoblank()
Dim r, c, rowstart, rowend, colstart, colend As Long
rowstart = Selection.Row
rowend = Selection.Rows.Count + rowstart - 1
colstart = Selection.Column
colend = Selection.Columns.Count + colstart - 1
For c = colstart To colend
For r = rowstart To rowend
If Cells(r, c) = 0 Then Cells(r, c) = ""
Next r
Next c
End Sub

 

[danpt]

Many thanks, Jim

 

[Dave Peterson]

Could you just select the range
Edit|replace
what: 0
with: (leave blank)
replace all

Record a macro when you do it manually and you'll have the code.

 

[Jim Cone]

You are welcome.
'--
Jim Cone



"danpt"
<[email protected]>
wrote in message
Many thanks, Jim

 

[danpt]

It wouldn't work with virgin cells in sheets that had never been used.
Sub Macro1()
Workbooks("ABC.xls").Sheets("Sheet3").Select
Workbooks("ABC.xls").Sheets("Sheet3").Range("C7:F12").Select
Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
Please explain.
Thank you, Dave

 

[Dave Peterson]

I thought you wanted to replace the 0's with an empty cell.

That's different from replacing the empty cells with a 0.

And if you really wanted to replace the empty cells with a 0 using edit|replace
(or the equivalent in a macro), you'll have to make sure that the last used cell
is at least as far to the bottom right as you need.

If you hit ctrl-end, you'll be taken to this last used cell. If that's not far
enough, then just add some temporary text to where you want it to be. Then
clear that cell when you're done.

 

[Gord Dibben]

You recorded just the opposite of what you originally asked for.

I need a vba code to replace all the cells having 0 value with empty cell

Virgin cells do not contain "" so no point looking for them.


Gord Dibben MS Excel MVP

 

[danpt]

Thank you all,
The reason is that I need to switch between "" and 0 in a chart range, in
order to use autochart for better scaliing.
I use 0 to indicate non active event.
Sorry if I misleaded you.