How to Use COUNTA Function in VBA (With Examples)

You can use the CountA method in VBA to count the number of non-empty cells in a specific range.

Here is one common way to use this method in practice:

Sub CountARange()
    Range("C1") = WorksheetFunction.CountA(Range("A1:A10"))
End Sub

This particular example counts the number of non-empty cells in the range A1:A10 and outputs the result in cell C1.

If you would instead like to display the number of non-empty cells in a message box, you can use the following syntax:

Sub CountARange()
    'Create variable to hold results of CountA
    Dim counta As Single
    
    'Calculate number of non-empty cells in range
    counta = WorksheetFunction.CountA(Range("A1:A10"))
    
    'Display the result
    MsgBox "Number of Non-Empty Cells in Range:" & counta 
End Sub

The following examples shows how to use each of these methods in practice with the following column of values in Excel:

Example 1: Count Non-Empty Cells in Range and Display Results in Cell

Suppose we would like to calculate the number of non-empty cells in the range A1:A10 and output the results in a specific cell.

We can create the following macro to do so:

Sub CountARange()
    Range("C1") = WorksheetFunction.CountA(Range("A1:A10"))
End Sub

When we run this macro, we receive the following output:

Notice that cell C1 contains a value of 7.

This tells us that there are 7 non-empty cells in the range A1:A10.

Example 2: Count Non-Empty Cells in Range and Display Results in Message Box

Suppose we would instead like to count the number of non-empty cells in the range A1:A10 and output the results in a message box.

We can create the following macro to do so:

Sub CountARange()
    'Create variable to hold results of CountA
    Dim counta As Single
    
    'Calculate number of non-empty cells in range
    counta = WorksheetFunction.CountA(Range("A1:A10"))
    
    'Display the result
    MsgBox "Number of Non-Empty Cells in Range:" & counta 
End Sub

When we run this macro, we receive the following output:

The message box tells us that there are 7 non-empty cells in the range A1:A10.

Note that if you’d like to instead count the number of non-empty cells in an entire column you could type A:A instead.

This will calculate the number of non-empty cells in all of column A.

Note: You can find the complete documentation for the VBA CountA method here.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Write AVERAGEIF and AVERAGEIFS Functions
VBA: How to Write SUMIF and SUMIFS Functions
VBA: How to Write COUNTIF and COUNTIFS Functions