Forum Discussion

PondyDave's avatar
PondyDave
Copper Contributor
Jul 24, 2019

Excel counta is counting blank cells as if they had data. A lengthy fix is given

My mega spreadsheet is full of invisible junk scattered across cells. It is 1300 rows deep by 500 columns wide. Each bit of junk appears in the totals when I counta the rows or columns. I discovered ...
excel
jude234's avatar
jude234
Copper Contributor
Nov 30, 2023

ahahahaha nooooo

The cells did have a formula in them that returned "", i.e. they had an iferror on an index/match, and returned nothing. And excel counts them as containing text. But the formula is gone, with copy/paste values. So the cell contains nothing to you and me, but it sorts and counts as if it contained data. 

It just happened to me. Thanks to the OP, fixed now.

Super irritating excel moment, 3 stars.

 

PReagan 

JKPieterse's avatar
JKPieterse
Silver Contributor
Nov 30, 2023

jude234 

You all could have used a bit of VBA code and sit back and relax:

 

Sub ClearCellsEmptyText()
    Dim cl As Range
    Dim v As Variant
    For Each cl In ActiveSheet.UsedRange
        If not cl.HasFormula Then
            v = cl.Value
            'Remove Enter, line break, carriage return, non-breaking space, spaces
            v = Replace(v, Chr(160), "")
            v = Replace(v, Chr(10), "")
            v = Replace(v, Chr(12), "")
            v = Replace(v, Chr(13), "")
            v = Trim(v)
            'If nothing remains, clear the cell
            If Len(v) = 0 Then
                cl.ClearContents
            End If
        End If
    Next
End Sub

Resources