VBA COUNTA

COUNTA Worksheet Function in Excel VBA

In our earlier article “Excel COUNTA,” 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. In this article, we will show you how to use the COUNTA function in VBA to count all the cell values in the supplied range.

Examples of COUNTA Function in VBA

One of the truth is the “COUNTA” function is not a VBA function. I know your question is, if it is not a VBA function, then 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.

Ok, let’s write the code to apply the excel VBA COUNTA.

Step 1: Create a subprocedure name.

Step 2: Now, first 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.

Code:

Sub Counta_Example1()

 Range("C2").Value =

End Sub

Step 3: In cell C2, we need the value of the VBA COUNTA function. So to apply the excel VBA COUNTA function, lets first use the worksheet function class.

Code:

Sub Counta_Example1()

 Range("C2").Value = Work

End Sub

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

Code:

Sub Counta_Example1()

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

End Sub

Step 5: Now, we need to supply the range of cells to be counted. In this example, we need to calculate the range of cells from A1 to A11. To provide the cells using the VBA RANGE object.

Code:

Sub Counta_Example1()

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

End Sub

Ok, let’s run the code to get the result in cell C2.

So, the same result returned by VBA COUNTA as well.

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

Coding with Variables

VBA variables are the key to building a project. Now for the same data, we can declare VBA variables and arrive at the result.

For example, look at the below code.

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’s explain the above code now.

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

Dim CountaRange As Range

Next, I 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, I have set the cell as C2.

Set CountaResultCell = Range("C2")

Now, as usual, I have applied the COUNTA function using variables instead of hardcoded ranges. Now, look at the old code and this VBA code.

Code 1:

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 C2 cell. So this variable is 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.

This is what is the difference between old code and code with variables.

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

Recommended Articles

This has been a guide to VBA COUNTA. Here we learn how to use COUNTA Worksheet Function in Excel VBA and examples and download an excel template. Below are some useful excel articles related to VBA –

• Exit Subprocedure in Excel VBA
• Declare One Dimensional Array in VBA
• Val Functions in VBA
• COUNTIF Function in VBA