Forum Discussion

Copper Contributor

Mar 06, 2022

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 b...

excel

Formulas and Functions

HansVogelaar

MVP

Mar 07, 2022

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<>""))

4Jacks

Copper Contributor

Mar 12, 2022

Sumproduct worked. Thank you.