VBA COUNTA

What is COUNTA Worksheet Function in Excel VBA?

COUNTA is a worksheet function which can be used in VBA to count all the non-blank cells irrespective of the values in it. VBA COUNTA stands for “Count Anything” whereas COUNT is a function which counts only numerical values in the given range of cells. VBA COUNTA function can count values like numbers, string, special characters, error values (#N/A, #DIV/0!, #NAME?) space characters, etc…. The output returned by the VBA COUNTA function is numerical value hence when we try to assign the output to a variable we should be aware of the data type of the variable.

For example, look at the following data.

We can use the following code to get the total non-blank cells in the range A2 to A10. This count will print the total number of non-blank cells in cell D1.

Syntax of VBA COUNTA Worksheet Function

Following is the syntax of the VBA COUNTA function.

Targeted Cells or Variable Name = WorksheetFunction.CountA(Arg1, [Arg2], [Arg3], …) As Double

Targeted Cells or Variable Name: It represents the area where we must store the result of the VBA COUNTA function. It could be a cell or a variable.

WorksheetFunction.CountA(: Since COUNTA is a part of worksheet function category we must use the keyword WorksheetFunction to access the COUNTA function.

Arg1: This is a mandatory argument. Here, we provide the cell address or range of cells where we want to count the data cells. The cell range address could be a direct cell range, or it could be a variable which holds the range reference.

[Arg2]: These arguments are optional.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How to Use COUNTA Worksheet Function in Excel VBA?

Let us show you a practical example of VBA COUNTA function stepwise. For example, look at the following data.

We have data in the range A2 to A12 with some empty cells in between. Let us follow the steps below to apply the VBA COUNTA function to get the total cells count containing the data in the range A2 to A12 in cell D2.

Step 1: Start the sub-procedure by naming the macro.

Step 2: The output of the VBA COUNTA function returns a scalar value. Hence, we need to store it in a cell, i.e., cell D2. Refer the cell with RANGE object along with the VALUE property of the Range object.

Step 3: Access Worksheet function property to get the COUNTA function.

Step 4: Once the Worksheet Function is accessed, enter a dot. It will show the list of all the worksheet functions available as part of VBA coding.

From this list, choose the VBA COUNTA function.

Step 5: For theVBA COUNTA function. We must provide the range of cells from which we would like to count all the non-blank cells. Here, VBA COUNTA range is A2 to A12 hence provide the cell range from A2 to A12.

Step 6: Close the bracket to end the VBA COUNTA function. The following is the complete code for you.

Sub Counta_Basic()
Range(“D2”).Value = WorksheetFunction.CountA(Range(“A2:A12”))
End Sub

Let’s execute this code by pressing the F5 function key and we should get the total non-blank cells count in cell D2.

The total number of non-blank cells in the range A2 to A12 is 9 and the same has been returned by the VBA COUNTA function.

Examples of COUNTA Function in Excel VBA

Let us see some useful real-world examples of using the VBA COUNTA function.

Example #1: VBA COUNTA as Supporting Function for VBA IF Condition

VBA COUNTA is often used as a supporting function alongside VBA IF condition. For example, look at the following data in Excel.

We have people’s names and they have attended some training in 5 sessions. While maintaining the tracker to update the status they have not followed the consistent pattern.

For example, some people entered Yes, and some people have entered 1 instead of Yes. However, we need to arrive at the status “Completed” if the total count of cells of 5 training sessions is greater than or equal to 4. While counting the cells the values don’t matter but we need to count all the non-blank cells.

We can use the VBA COUNTA function with VBA IF condition to check the non blank cells and arrive at the status column.

The following code will help us to arrive at the status based on VBA COUNTA condition input given for the VBA IF condition.

Sub Counta_Example1()
‘Find the last used cell in the worksheet
Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
‘Define a variable to loop through all the cells
Dim K As Long
‘Initiate FOR NEXT loop
For K = 2 To LR
‘Use VBA COUNTA FUNCTION to check the total count of non blank cells
If WorksheetFunction.CountA(Range(“B” & K & “:F” & K)) >= 4 Then
‘If the cell count is >=4 then insert Completed in status column
Cells(K, 7).Value = “Completed”
Else
‘If the cell count is not >=4 then insert Not Completed in status column
Cells(K, 7).Value = “Not Completed”
End If
Next K
End Sub

We have data in more than one cell hence we have used the FOR NEXT loop to go through each cell and apply the VBA COUNTA logic to check if the count of cells in the given range is greater than or equal to 4 or not, if the count is greater than or equal to 4 then it will insert “Completed” status in status column or else it will insert “Not Completed” in status column.

Let’s execute the code and we will get the following result in status column.

We have got “Completed” for names “James” (row number 3) and “Ryan” (row number 6) because the VBA COUNTA function has returned total non blank cells count as 5.

Example #2: VBA COUNTA Treats Space as Non Blank Cell

One of the key features that we need to understand with the VBA COUNTA function is that it will count space as a character.

For example, look at the following data in Excel.

We have some values in range A2 to A8 and in cell D2 we need to calculate the total count of cells which are non blank.

The following code will get us the non blank cells count in the range A2 to A8.

Sub Counta_Example2()
Range(“D2”).Value = WorksheetFunction.CountA(Range(“A2:A8”))
End Sub

Once we run this code, we will get the following result in cell D2.

The total cells count is 7.

However, if we closely look at the data in range A2 to A8 we have 7 cells in total. Out of these 7 cells we have data in only 6 cells and remaining 1 cell is a blank. But the VBA COUNTA function has returned all the 7 cells as non blank cells.

This is very we need to be aware of junk data when we receive the file from some else. Let’s apply the LEN function to count the total characters in each cell.

In cell A4 we have 1 character which is invisible i.e., we have a space character hence the LEN excel function has returned the total number of characters in cell A4 as 1.

Hence, when we work with the VBA COUNTA function, we need to keep in mind that space is also a character hence the VBA COUNTA function will treat that cell as non blank cell.

Example #3: VBA COUNTA COLUMN and VBA COUNTA ROW

We can use the entire column or row as a reference in the VBA COUNTA function. For example, look at the following codes.

Sub Counta_Example3()
‘Count all the cells in column A
Range(“D2”).Value = WorksheetFunction.CountA(Range(“A:A”))
‘Count all the cells in the row 10
Range(“D2”).Value = WorksheetFunction.CountA(Range(“10:10”))
End Sub

The first line of the code will count all the cells in the column A, this is called VBA COUNTA Column and the second lime of the code will count all the cells in the row 10, and this is called as VBA COUNTA Row.

Coding with Variables

Variables are most useful when we need to work with larger data sets. We can take the help of variables in VBA COUNTA function to get the count of non blank cells.

For example, look at the following data.

Let’s develop the code with variables.

The following code is the code with variables reference.

Part #1: Here we have defined a variable and set counting range A2 to A7 to the variable Count_Range.

Part #2: Here we have defined another variable and set the result cell D2 to the variable Result_Cell.

Part #3: Here we have used the VBA COUNTA function with the help of variables defined in Part #1 and #2.

Once we run the code, we will get the following result in the result cell D2.

However, this is not the VBA COUNTA dynamic range count because if some data is added to the list, then it will not take the newly added data into consideration.

For example, look at the following data.

We have added two new products in rows 8 and 9. The current code will not take these newly added cells into consideration because the data range given in the previous code is fixed i.e., A2 to A7.

This is where we need to implement the logic to get the data range dynamically. The following code will find the data range dynamically and returns the count of cells in the complete range of cells.

Compared to the previous code, only the yellow background colored code is the newly added elements.

The variable LR is used to find the last used row in column A and then we use this variable to set the data range i.e., A2:A&LR

In this way, we can set the data range dynamically for VBA COUNTA function.

Important Things to Note

• VBA COUNTA is not a VBA function; we can use it as part of worksheet function collection only.
• VBA COUNTA function returns only numerical value as output.
• When we need to count the data in entire column or row, then we need to provide the entire column or row reference in double quotes.
• VBA COUNTA function ignores all the empty cells from counting.

Frequently Asked Questions (FAQs)

Download Template

This article must help understand VBA COUNTA formulas and examples. We can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA COUNTA. We learn the Worksheet function called COUNTA to count all the non-blank cells with examples. You can learn more from the following articles –

• VBA Break For Loop
• VBA Const
• VBA ComboBox