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