I should point out that both of my suggestions *change* blanks to
zeros - which goes a bit deeper than changing how blanks are
displayed. In particular, many worksheet functions such as AVERAGE()
ignore blanks but treat zeros as values. It is possible to modify both
suggestions to have the *string* zero replacing blanks (with a change
in text alignment making them look like ordinary zeros) and this will
help (?) with some functions such as AVERAGE() but will still cause
some functions lkie COUNTBLANKS() to behave differently. The bottom
line is that it seems you can only display values, and a cell with a
value is no longer blank.
Hth
-John Coleman
On Mar 15, 5:21 pm, "John Coleman" <jcole...@franciscan.edu> wrote:
> I don't think that there is any format setting that will do this for
> you. It is possible to quickly convert blanks to cells without any
> VBA:
>
> 1) select the range of cells that you are interested in
> 2) hit F5 or type CtrlG to get the GoTo window displayed
> 3) select special
> 4) select blanks - hit ok
> 5) type 0 then ctrl+enter
>
> here is a vba sub to convert blank cells to zeros in a range of cells:
>
> Sub Zero(R As Range)
> Dim cl As Range
> For Each cl In R.Cells
> If IsEmpty(cl.Value) Then cl.Value = 0
> Next cl
> End Sub
>
> Sub test()
> Zero Range("A1:C5")
> End Sub
>
> you could probably link this with a change event if you want a cell to
> display 0 when you hit it.
>
> Hth
>
> -John Coleman
>
> On Mar 15, 12:57 pm, michael.holo...@synovate.com wrote:
>
>
>
> > I need to format an excel worksheet to show "Zero" in cells that are
> > blank.
>
> > I understand how to show them when you type in Zero (0) but cannot get
> > it to show Zero when the cell is blank.
>
> > Any ideas would be very helpfull- Hide quoted text -
>
> - Show quoted text -
|
Similar Threads
