Forum Discussion
COUNTA counting blanks that were saved as value
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?
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
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 SubOption 2:
Instead of COUNTA, use SUMPRODUCT:
=SUMPRODUCT(--(range<>""))- Sumproduct worked. Thank you.
