Is there an alternative method to counting a column's non-blank cells than the below method? The method below does the trick, but when there is no data in the column, the script generates an No Cells Were Found Error. Looking for a script that just returns 0 in the event of no cells instead of the error.

Worksheets("Sheet1").Range("A2:A30000").Cells.SpecialCells(xlCellTypeConstants).Count

CodePudding user response：

Count Non-Blank or Non-Empty Cells in a Column

• The code was used in a new workbook. In Sheet1, in cell A2 I entered the formula ="" and in cell A3 I entered a single quote ('). These two cells appear to be empty, but they are not. They are blank.

Sub CountNonBlanks()

    Dim rg As Range
    Set rg = ThisWorkbook.Worksheets("Sheet1").Range("A2:A30000")

    ' Counts cells that are not empty, do not contain a formula
    ' that evaluates to an empty string (e.g. '=""')
    ' and do not contain a single quote.
    Dim NonBlanks As Long
    NonBlanks = rg.Cells.Count - Application.CountBlank(rg)
    
    ' Counts cells that are not empty.
    Dim NonEmpties As Long
    NonEmpties = Application.CountA(rg)

    Debug.Print rg.Address(0, 0), NonBlanks, NonEmpties

End Sub

• The result in the Immediate window was:

  A2:A30000      0             2