Filling Blank Cells with Zeroes

Background

Often, you will import a set of data and many of the cells are blanks. With financial data you often need these cells to be set to zero (0) instead of being blank. The code to the right will fill all blank cells in your selection to a 0.

The procedure uses the For Next loop to go through each cell in your selection and if the value is blank (“”) or ({Space}) the value of the cell will be changed to zero (0).

Code

Sub replaceBlankWithZero()
     Dim rng As Range
     For Each rng In Selection
          If rng = "" Or rng = " " Then
               rng.Value = "0"
          End If
     Next rng
End Sub 

Leave a Reply

Your email address will not be published.