Excel - Replacing 0 value with empty

Asked By danp on 19-Feb-09 09:31 PM
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 replied on 19-Feb-09 09:42 PM
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




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
danp replied on 19-Feb-09 09:58 PM
Many thanks, Jim
Dave Peterson replied on 19-Feb-09 10:00 PM
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 will have the code.




--

Dave Peterson
Jim Cone replied on 19-Feb-09 10:04 PM
You are welcome.
'--
Jim Cone



Many thanks, Jim
danp replied on 21-Feb-09 04:29 PM
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 replied on 21-Feb-09 04:43 PM
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.




--

Dave Peterson
Gord Dibben replied on 21-Feb-09 04:53 PM
You recorded just the opposite of what you originally asked for.


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


Gord Dibben  MS Excel MVP
danp replied on 22-Feb-09 02:04 PM
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.