Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Zero's (0) in blank cells
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Zero's (0) in blank cells
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, 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display Zero's (0) in blank cells
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" 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, 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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Display error msg when cells left blank | Excel Programming | |||
=SUMPRODUCT formula is counting the blank cells as well as zero's | Excel Worksheet Functions | |||
=SUMPRODUCT formula is counting the blank cells as well as zero's | Excel Worksheet Functions | |||
Chart to display only bars for values that are > 3 & blank cells | Excel Worksheet Functions | |||
PivotTable (blank) cells won't display NULL! | Excel Programming |