COUNTA counting blanks that were saved as value

Copper Contributor
I need to delete rows of cells that have no data.

I was going to save formulae as values.

Then the cell would be empty.

Then countA to sum the cells with data and then filter out everything but zeros and delete the remaining rows that are empty.

However, every formula I’ve used is counting the blank field. If I copy a previously unused cell (general format) on top of the cell that I saved as a value, IT is not being counted.

How do I save as a value with a format that will not be counted when empty?
2 Replies

@4Jacks 

Option 1:

Instead of Paste Values, select the range with formulas and run the following macro (I have assigned it to a Quick Access Toolbar button for ease of use):

Sub Formulas2Values()
    On Error Resume Next
    Selection.Value = Selection.Value
End Sub

Option 2:

Instead of COUNTA, use SUMPRODUCT:

=SUMPRODUCT(--(range<>""))
Sumproduct worked. Thank you.