Updated on January 1, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

COUNTA Worksheet Function in Excel VBA

In our earlier article “Excel COUNTAExcel COUNTAThe COUNTA function is an inbuilt statistical excel function that counts the number of non-blank cells (not empty) in a cell range or the cell reference. For example, cells A1 and A3 contain values but, cell A2 is empty. The formula “=COUNTA(A1,A2,A3)” returns 2. read more,” we have seen how to use the COUNT function to count the numerical values from the range of values. How about calculating all the costs in the field of cells? Yes, we can estimate that as well. To count all the cell values in the range of cells, we need to use the formula “COUNTA” in Excel VBA. This article will show you how to use the COUNTA function in VBA to count all the cell values in the supplied range.


You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA COUNTA (wallstreetmojo.com)

Examples of COUNTA Function in VBA

One truth is that the COUNTA function is not a VBA functionVBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more. So your question is if it is not a VBA function, how do we use it? Nothing worries, even though it is not a VBA function; still, we can use it under the worksheet function class in VBA coding.

You can download this VBA COUNTA Excel Template here – VBA COUNTA Excel Template

Let us write the code to apply the Excel VBA COUNTA.

Step 1: Create a subprocedure name.

Step 2: Now, decide where we will store the result of the VBA COUNTA function. In this example, I want to keep the work in cell C2. So my code will be Range(“C2”).Value.


Sub Counta_Example1()

 Range("C2").Value =

End Sub
VBA Counta Example 1

Step 3: In cell C2, we need the value of the VBA COUNTA function. So, to apply the Excel VBA COUNTA function, let’s first use the worksheet function class.


Sub Counta_Example1()

 Range("C2").Value = Work

End Sub
VBA Counta Example 1-1

Step 4: After applying the worksheet function class, select the formula COUNTA by putting a dot.


Sub Counta_Example1()

 Range("C2").Value = WorksheetFunction.Count

End Sub
VBA Counta Example 1-2

Step 5: We need to supply the range of cells for counting. In this example, we need to calculate the range of cells from A1 to A11. Then, to provide the cells using the VBA RANGE objectUsing The VBA RANGE ObjectRange is a property in VBA that helps specify a particular cell, a range of cells, a row, a column, or a three-dimensional range. In the context of the Excel worksheet, the VBA range object includes a single cell or multiple cells spread across various rows and columns.read more.


Sub Counta_Example1()

 Range("C2").Value = WorksheetFunction.CountA(Range("A1:A11"))

End Sub
VBA Counta Example 1-3

Let us run the code to get the result in cell C2.

VBA Counta Example 1-4

So, the VBA COUNTA returns the same result as well.

Like this, we can use COUNTA to count the non-empty or non-blank cells from the supplied range.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Coding with Variables

VBA variables are the key to building a project. Now, we can declare VBA variablesDeclare VBA VariablesVariable declaration is necessary in VBA to define a variable for a specific data type so that it can hold values; any variable that is not defined in VBA cannot hold values.read more and arrive at the result for the same data.

For example, look at the below code.


Sub Counta_Example2()

 Dim CountaRange As Range
 Dim CountaResultCell As Range

 Set CountaRange = Range("A1:A11")
 Set CountaResultCell = Range("C2")

 CountaResultCell = WorksheetFunction.CountA(CountaRange)

End Sub

Let us explain the above code now.

First, we have declared the variable “CountaRange” as a range to reference the range of values.

Dim CountaRange As Range

Next, we have set the reference as range A1 to A11.

Set CountaRange = Range("A1:A11")

The second variable is to reference the COUNTA result cell.

Dim CountaResultCell As Range

For this variable, we have set the cell as C2.

Set CountaResultCell = Range("C2")

As usual, we have applied the COUNTA function using variables instead of hardcoded ranges. Now, look at the old code and this VBA codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

Code 1:

Code 1

Code 2:

Code 2

In code 1, we have Range C2. In Code 2, we have the variable name “CountaResultCell.” Here the variable “CountaResultCell” set reference as a C2 cell. So this variable is a C2 cell now.

In code 1, the COUNTA function range is A1 to A11. In regulation 2, it is a variable called “CountaRange.” This variable holds a reference to the range A1 to A11.

That is the difference between old code and code with variables.

So, the COUNTA function helps us count all the non-empty cells from the supplied range irrespective of the data.

Recommended Articles

This article has been a guide to VBA COUNTA. Here, we learn how to use the COUNTA worksheet function in Excel VBA and examples and download an Excel template. Below are some useful Excel articles related to VBA: –