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

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"
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

Many thanks, Jim


"Jim Cone" wrote:

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"
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

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.



danpt wrote:

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

--

Dave Peterson

You are welcome.
'--
Jim Cone



"danpt"

wrote in message
Many thanks, Jim




"Jim Cone" wrote:
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"
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

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:F1 2").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" wrote:

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.



danpt wrote:

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

--

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.



danpt wrote:

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:F1 2").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" wrote:

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.



danpt wrote:

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

--

Dave Peterson


--

Dave Peterson

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

On Sat, 21 Feb 2009 13:29:01 -0800, danpt
wrote:

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:F1 2").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" wrote:

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.



danpt wrote:

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

--

Dave Peterson

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.

"danpt" wrote:

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