Introduction

In this article we'll be covering the Excel VBA CountA function, usage, and some basic examples.

The Excel VBA CountA

Sometimes you have an array of cells where some cells contain data and some don't. In a scenario like this to count the number of cells that contain data you use the CountA function. It returns a count of the number of cells in that range that have values.

To see how many cells have values in the range of B2:B10 you would use the formula =CountA(B2: B10) and to count how many cells have values in the range of C2:C10 you would use the formula =CountA(C2: C10).

Let's see those examples in action below:

With VBA or Visual Basic for Applications, we can create a macro and run the code, and it will give us the number of values in a range of cells using CountA. See the below samples:

Sub example1()  

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

End Sub  

Code explanation:

Range("A11") - this defines the cells where we want the result of the macro code to be stored.

**. Value ** - this refers to an argument that defines what kind of action we want to do.

WorksheetFunction.CountA - It refers to the syntax of the function we are currently encoding on.

(Range("A1: A10") - this defines as the range or the cells we want to evaluate.

See below result for the above code:

As you can see, A11 is where the result is placed. As you can see the VBA CountA function counts cells with a valid value.

CountA using variables

Variables are very important when it comes to VBA and macro coding. See the below sample code where we declare VBA variables:

Code:

Sub example2()  

Dim CountaRange As Range  
Dim CountaResultCell As Range  

Set CountaRange = Range("A1:A10")  
Set CountaResultCell = Range("B2")  

CountaResultCell = WorksheetFunction.CountA(CountaRange)  

End Sub  

Code explanation:

CountaRange - this is used as the range for VBA to know that referenced range of values.

Range("A1: A10") - this is used for VBA to know the range of the cells we wished to evaluate.

Dim CountaResultCell As Range - this is used for us to set the cell B2 as the range value wherein we will be storing the result.

Let's take a look at the results for the above code:

Another example:

Code:

Sub example3()  

Dim CountValues As Variant  
CountValues = Application.WorksheetFunction.CountA(Range("A1:D10"))  
MsgBox ("CountA result is: " & CountValues)  

End Sub  

Code explanation:

From ranges A1 to D10 the code will count all the cells with values and the result will be shown in the message box.

Let's go over another example:

Code:

Sub example3()  

Dim CountValues As Variant  

CountValues = Application.WorksheetFunction.CountA(Range("A1:C10"), Range("F1:H10"))  


End Sub  

We have added the other range where values are located by adding **, Range("F1: H10")**.

Conclusion

We've gone over why we use the CountA function and some typical examples of its usage. We hope you can use the CountA function to create cleaner and more powerful worksheets in Excel.