blank cells

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

Is there a way to make a spreadsheet show "0" or "-" for
blank cells? In other words without having to actually
enter a zero in the cell, make it automatically show "0"
or "-".
 
How about a little macro like this?

Sub FillBlanks()
Dim cell As Range
For Each cell In Selection
If Len(cell) = 0 Then
cell.Value = "-"
End If
Next
End Sub

--
Press ALT+F11, go to Insert > Module, and paste this in
the window. Go back to XL, select your range of cells, go
to Tools > Macro > Macros and run the macro "FillBlanks".

HTH
Jason
Atlanta, GA
 
Jason, I haven't tried this, but might it also wipe out cells that contain
formulas which result in "" ?
 
Is there a way to make a spreadsheet show "0" or "-" for
blank cells? In other words without having to actually
enter a zero in the cell, make it automatically show "0"
or "-".
Click to expand...

If you mean truly blank cells, no. Cells must have contents of some sort in
order for Excel to display anything in them. If you mean cells evaluating to "",
you use a number format like 0.00;-0.00;0.00;\- but any text value would then
appear as - .
 
Hi
one way:
Sub FillBlanks()
Dim cell As Range
For Each cell In Selection
If (Len(cell) = 0) and (not cell.hasformula) Then
cell.Value = "-"
End If
Next
End Sub
 
Me neither a VBAer..

but maybe ISBLANK(cell) ... now I'm not sure whether it would be If
Isblank(cell) = 1 then .... or whether it needs to be multiplied by 1, like
If 1*isblank(cell) = 1 , or maybe it doesn't need to be anything but true
and doesn't need another equal sign.

...
 
Replace the statement : If Len(cell) = 0 Then
with : If IsEmpty(cell) Then

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Dave

Yes, it will wipe out these formulas resulting in ""

Edit the macro to........

Sub FillBlanks()
Dim cell As Range
For Each cell In Selection
If Not cell.HasFormula Then 'add this line'
If Len(cell) = 0 Then
cell.Value = "-"
End If
End If
Next
End Sub

Gord Dibben Excel MVP
 
...
...
If Not cell.HasFormula Then 'add this line'
If Len(cell) = 0 Then
cell.Value = "-"
End If
End If
Click to expand...
...

Why not either

If cell.Formula = "" then cell.Value = "-"

or

If IsEmpty(cell.Value) Then cell.Value = "-"

?
 
Is there a way to make a spreadsheet show "0" or "-" for
blank cells? In other words without having to actually
enter a zero in the cell, make it automatically show "0"
or "-".
Click to expand...

Just in case the others are correct that you'd accept entry into previously
blank cells, don't bother with macros. Select the entire range in which you want
blank cells to show -, press [F5], click on the Special... button, in the next
dialog select Blanks, click OK. At this point all the blank cells should be
selected. Type a single hyphen, hold down a [Ctrl] key and press [Enter].
 
Is there no "isblank()" within VBA?


Gord Dibben said:
Dave

Yes, it will wipe out these formulas resulting in ""

Edit the macro to........

Sub FillBlanks()
Dim cell As Range
For Each cell In Selection
If Not cell.HasFormula Then 'add this line'
If Len(cell) = 0 Then
cell.Value = "-"
End If
End If
Next
End Sub

Gord Dibben Excel MVP
Click to expand...
 
Is there no "isblank()" within VBA?
Click to expand...
...

No. It's not needed. Cells for which the worksheet function ISBLANK would
evaluate TRUE have Empty values. Wonderful that Excel and VBA use different,
colloqually almost equivalent terms for more or less the same thing. Anyway, VBA
provides an IsEmpty() function for which IsEmpty(RangeObject),
IsEmpty(RangeObject.Value), and IsEmpty(RangeObject.Value2) all return the same
result as ISBLANK(INDEX(RangeRef,1,1,1)).
 
Back
Top