Automatically fill empty cell with default value i.e. 0

[altric]

Hi everyone,

Example:

Name Qty Cost Total
ABC 10 1.00 10.00
BC 0.00
AC 10 2.00 20.00
C 2 1.00 2.00


I would like to use a macro to auto fill up the "Qty" and "Cost" field
with 0 (zero) if they are blank or empty.

May I know how to achieve it?

Thank you.

 

[Ken Johnson]

Hi everyone,

Example:

Name Qty Cost Total
ABC 10 1.00 10.00
BC 0.00
AC 10 2.00 20.00
C 2 1.00 2.00


I would like to use a macro to auto fill up the "Qty" and "Cost" field
with 0 (zero) if they are blank or empty.

May I know how to achieve it?

Thank you.

Hi altric,

try this macro on a copy of your data...

Public Sub Blank_to_zero()
Dim rngCell As Range
Dim rngRange As Range
On Error GoTo CANCELLED
Set rngRange = Application.InputBox( _
Title:="Blanks to Zeros", _
Prompt:="Select cells for changing blanks to zeros.", _
Default:=Selection.Address, _
Type:=8)
For Each rngCell In rngRange
With rngCell
If .Value = "" Then Let .Value = 0
End With
Next rngCell
CANCELLED:
End Sub

Ken Johnson

 

[Roger Govier]

Hi

For a non-macro solution.
Enter 0 in a spare cell on your sheet, mark the cell >Copy
Mark the complete range of values in B2:C4>Paste Special>Add

 

[Dave Peterson]

Another non-macro solution:

Select the range to fix (include the filled in cells)
edit|goto|special
check Blanks
type 0
but hit ctrl-enter (instead of enter) to fill all the empty cells with 0's.

It could look like this in code:

Dim myRng As Range
Set myRng = Selection

On Error Resume Next
myRng.Cells.SpecialCells(xlCellTypeBlanks).Value = 0

 

[Lori]

Another solution: Choose Edit -> Replace with nothing in the Find box
and Replace box set to 0