VBA Count

Count Function in VBA Excel

In VBA Count function is used to count that how many number of cells have values in it, we need to remember that the cells which have numbers or text which are enclosed in double quotes or those cells whose values are typed directly are counted, those cells which have the random data which cannot be translated by excel are not counted.

In VBA COUNT is a worksheet function which can be applied through worksheet function class. It is not a built-in function. VBA COUNT function can count all the numerical values from the supplied range of values.

In this article, we will show you how to use the COUNT function in VBA.

Examples of VBA Count Function

Let’s take some practical examples of VBA Count Function to understand its working.

VBA Count Example #1

Let write the code on your own to apply this COUNT function.

Step 1: Start the macro by starting the Sub procedure.

Code:

Sub Count_Example1()

End Sub

Step 2: Now we will store the result of COUNT function in cell C2. So our code should be as “Range(“C2”).Value =

Code:

Sub Count_Example1()

 Range("C2").Value =

End Sub

Step 3: Since it is not a VBA built-in function we need to apply through Worksheet Function class. So access through worksheet function class.

Code:

Sub Count_Example1()

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

End Sub

Step 4: Now supply the range as A1 to A7.

Code:

Sub Count_Example1()

Range("C2").Value = WorksheetFunction.Count(Range("A1:A7"))

End Sub

Ok, these are three simple steps to arrive the result.

Now run the code to see the result in cell C2.

As usual, we got the result as 3 like how we got when we applied VBA COUNT function in a worksheet.

If observe the formula bar we have not got the formula rather we just got the result of the formula. To apply the formula to the cell C2 we need to alter our code. Below is the code to apply the formula.

Code:

Sub Count_Example1()

    Range("C2").Value = "=Count(A1:A7)"

End Sub

Run this VBA code using F5 key or manually then, this will apply the formula to the cell C2.

VBA Count Example #2

Now, look at one more example with COUNT function. For this example look at the below data.

We have a few numerical & non-numerical values from A1 to A11. Now I will apply below code to count the numerical values from the range A1 to A11.

Code:

Sub Count_Example2()

    Range("C2").Value = "=Count(A1:A11)"

End Sub

When we execute the above code manually or using the F5 key, we got the result as 5.

However, when we close look at the data from A1 to A11 we have numerical values in cells A1, A2, A3, A4, A5, A7, and A11. So in total, we have numerical values in 7 cells but the formula has returned the result as only 5.

The reason for this is when we closely look at each cell in cell A5 and A7 the values are stored as text, not as numbers. So COUNT function has returned the result as only 5 because it treats those cells which formatted or stored as text as non-numerical values and ignores from the numerical count.

Like this, we can use COUNT function in excel as part of both worksheet and VBA function to count the numerical values from the list.

Recommended Articles

This has been a guide to VBA Count. Here we learn how to use VBA Count Function to count numerical values in Excel along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –

• VBA Remove Duplicate Values
• New Line in VBA
• VBA Split Sentence
• End in VBA 
• Union in VBA
• Using ArrayList in VBA
• VBA Enumeration
• INT function in VBA