Fill in zeros within a specified range

  • Thread starter Thread starter electricbluelady
  • Start date Start date
E

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
 
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
 
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
Click to expand...

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
 
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
 
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
 
Hi "Gary"s Student":
Thank you so much! It ran like a dream. You are awesome!
Regards,
 
Hi Jessen,
Thank you so much! You came to my rescue. I truly appreciate it. :)
Regards,
 
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,
 
Hi Rick,
Thank you for your response. It worked perfectly. I appreciate your help!
Regards,,
 
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.
 
Back
Top