How to generate a truly empty cell - "" does not work

[paulkaye]

"" generates a zero-length string, not a truly empty cell. This is
causing problems elsewhere. I'd like to find an output for an IF
statement that will give me a truly empty cell. The current formula
is:

=IF(COUNT(C24:C29)>0,SUM(C24:C29),"")

Any ideas? If it involves a macro (as I think it might, having read
other posts), please explain how to implement it.

Thanks!

 

[Niek Otten]

<This is causing problems elsewhere>

It shouldn't. Don't use ISBLANK(A1), use A1=""

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| "" generates a zero-length string, not a truly empty cell. This is
| causing problems elsewhere. I'd like to find an output for an IF
| statement that will give me a truly empty cell. The current formula
| is:
|
| =IF(COUNT(C24:C29)>0,SUM(C24:C29),"")
|
| Any ideas? If it involves a macro (as I think it might, having read
| other posts), please explain how to implement it.
|
| Thanks!

 

[JE McGimpsey]

Please don't keep starting new threads - it just tends to fragment any
answers you get, and to waste the time of those answering questions that
have already been answered.

If C24:C29 are calculated values, then put something like this in the
worksheet code module (right-click the worksheet tab and choose View
Code):

Private Sub Worksheet_Calculate()
With Range("C24:C29")
If Application.Count(.Cells) > 0 Then
Range("C30").Value = Application.Sum(.Cells)
Else
Range("C30").ClearContents
End If
End With
End Sub

If the values in C24:C29 are manually entered, use the Worksheet_Change
event instead - something like:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Range("C24:C29")
If Not Intersect(.Cells, Target) Is Nothing Then
If Application.Count(.Cells) > 0 Then
Range("C30").Value = Application.Sum(.Cells)
Else
Range("C30").ClearContents
End If
End If
End With
End Sub

 

[Niek Otten]

I just found out about the other thread you had already.

Five experts put efforts in answering your question. You turned your back on them and started a new thread.

Instead, try formulating your requirements more clearly. Not your question, but what you're trying to achieve.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| <This is causing problems elsewhere>
|
| It shouldn't. Don't use ISBLANK(A1), use A1=""
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|| "" generates a zero-length string, not a truly empty cell. This is
|| causing problems elsewhere. I'd like to find an output for an IF
|| statement that will give me a truly empty cell. The current formula
|| is:
||
|| =IF(COUNT(C24:C29)>0,SUM(C24:C29),"")
||
|| Any ideas? If it involves a macro (as I think it might, having read
|| other posts), please explain how to implement it.
||
|| Thanks!
|
|

 

[paulkaye]

Niek,

Great idea. It's made things a little less clean but it works -
thanks.

JE,

No probs - my apologies. I'm not really sure how everyone is viewing
this group and it seemed as though if the thread gets too old, it
drops off the radar. I'm using Google Groups where a new post to a
thread does not put it back to the top of the list.

Thanks guys,

Paul

 

[JE McGimpsey]

Well, there are various methods that "everyone is viewing this group"
with, but most of the 'regulars' are generally using some sort of
newsreaders (or OE) which can thread by reference or subject, rather
than a web portal like Google Groups. So *most* replies within threads
don't go unnoticed, even if the reply doesn't appear at "the top of the
list" (in my newsreader, newer posts are at the *bottom* of the list).

 

[Pete_UK]

I usually use Google Groups (unless it is acting up), and the latest
post (not just the latest thread) is shown first - click on Sort by
Reply in the left panel.

Pete

 

[Bob Phillips]

Paul,

I gave a solution in the other thread, no VBA.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[joeu2004]

No probs - my apologies. I'm not really sure how everyone is viewing
this group and it seemed as though if the thread gets too old, it
drops off the radar. I'm using Google Groups where a new post to a
thread does not put it back to the top of the list.

I use Google Groups, too. Simply click on "Sort by: Latest Message".