Fill in zeros within a specified range

[electricbluelady]

Hi everyone,
I have a range of 30 cells in a column, but most have numbers, and some are
blank. I need to write a procedure to replace blank cells with zeros, and to
skip over existing numbers. I need to keep this within the 30 cells in my
range. Any help would be greatly appreciated!
Thank you,
Electricbluelady

 

[Gary''s Student]

Select the top cell and run:

Sub zero_filler()
For i = 0 To 29
If IsEmpty(Selection.Offset(i, 0).Value) Then
Selection.Offset(i, 0).Value = 0
End If
Next
End Sub

 

[Per Jessen]

Hi everyone,
I have a range of 30 cells in a column, but most have numbers, and some are
blank. I need to write a procedure to replace blank cells with zeros, and to
skip over existing numbers. I need to keep this within the 30 cells in my
range. Any help would be greatly appreciated!
Thank you,
Electricbluelady

Hi

Try this macro, just change the TargetRange to suit your needs.

Dim TargetRange As Range

Sub FillWithZero()
Set TargetRange = Range("A1:A30")

For Each cell In TargetRange
If cell.Value = "" Then
cell.Value = 0
End If
Next
End Sub

Regards,
Per

 

[Susan]

try this:
(untested)

Sub electricbluelady()

dim myRange as range
dim r as range

set myRange = activesheet.range("a1:d50") '<=== change to suit range

for each r in myRange
if r.value = "" then
r.value = 0
end if
next r

End Sub

this will loop through each cell in the range, and if the range is
blank, it will insert a zero.
hope it helps!

susan

 

[Rick Rothstein \(MVP - VB\)]

Give this code a try (just define the range in the Set statement; everything
after the Set statement works automatically)...

Dim MyRange As Range
Set MyRange = Worksheets("Sheet3").Range("H8:H36")
If MyRange(MyRange.Count).Value = "" Then MyRange(MyRange.Count).Value = 0
MyRange.SpecialCells(xlCellTypeBlanks).Value = "0"

Rick

 

[electricbluelady]

Hi "Gary"s Student":
Thank you so much! It ran like a dream. You are awesome!
Regards,

 

[electricbluelady]

Hi Jessen,
Thank you so much! You came to my rescue. I truly appreciate it.
Regards,

 

[electricbluelady]

Hi Susan,
Thank you! It has helped a lot. I can see other places where I can run this
code. You made it much simpler. I sure appreciate it.
Regards,

 

[electricbluelady]

Hi Rick,
Thank you for your response. It worked perfectly. I appreciate your help!
Regards,,

 

[Dave Peterson]

Manually, you could select that range and then do
Edit|Replace
what: (leave blank)
with: 0
replace all

You could also:
Select the range
Edit|goto|Special|Blanks
type 0
but hit ctrl-enter to fill the empty cells.