CODEWITHSUNDEEP
exceldebugging
Lou
Lou

Reputation: 2509

Apparently "blank" cells still counted by COUNTA until updating the cell - is this a bug or feature?

I've noticed a behaviour in Excel which doesn't intuitively make sense.

If any formula causes a cell to evaluate as a blank string, such as ="", then you copy that cell and paste as value so that the formula disappears - the cell will still count when included in a COUNTA formula. Until you press F2 to edit the cell, and then press Enter - then the cell will no longer be counted.

I can understand why COUNTA counts cells including a formula, but once you copy and paste a cell as a value, if the formula evaluated to blank, the cell should also be blank, at least intuitively. What's weird is that updating the formula causes the COUNT to decrease. This seems like a bug, but I wanted to put it on here to be sure this wasn't a weird feature I was missing.

Here are the steps I took to reproduce this (also in the image below):

  1. Enter ="" into cells A1:A10
  2. Select cells A1:A10 and Copy
  3. Select cell A1 and Paste as Value
  4. Select any cell within the range and press F2
  5. Press Enter
  6. The count in cell A11 will decrease by one

Alternatively to Step 4, if you select any cell within the range and press Delete, the count will also decrease.

enter image description here

The count in the image is 7 because I've tested Steps 4 and 5 several times - the count started at 10.

I am using the Office Insider program, so if it is a bug this may be the reason why.

Can anyone shed some light on this?

Upvotes: 0

Views: 3213

Answers (1)

BigBen
BigBen

Reputation: 50008

Not a bug.

="" returns an empty string, which is not the same as a truly empty cell. Copy/pasting values maintains the empty string.

Editing the cell with F2 and then Enter effectively renders the cell blank.

Here's an interesting little VBA test for more detail:

Sub Test()
    With ActiveCell
        .Formula = "="""""           ' enter ="" in the ActiveCell
        Debug.Print TypeName(.Value) ' returns String

        .Copy
        .PasteSpecial xlPasteValues
        Debug.Print TypeName(.Value) ' returns String

        .Value = .Value
        Debug.Print TypeName(.Value) ' returns Empty
    End With
End Sub

Upvotes: 1

Related Questions