FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

# VBA Count

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

## Count Function in VBA Excel

VBA COUNT function, one can use to count how many cells have values in it. We must remember that it counts the cells with numbers or text enclosed in double quotes or whose values are typed directly. Those cells with random data that Excel cannot translate do not count.

In VBA, COUNT is a worksheet function that one can apply through the worksheet function class. It is not a built-in function. VBA COUNT function can count all the numerical values from the supplied range.

This article will show you how to use the COUNT function in VBA.

For eg:
Source: VBA Count (wallstreetmojo.com)

### Examples of VBA Count Function

Let us take some practical examples of the VBA COUNT function to understand its working.

You can download this VBA Count Function Template here – VBA Count Function Template

#### VBA Count Example #1

Write the code on your own to apply this COUNT function in Excel VBA.

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 the 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 must apply it through the 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```

These are three simple steps to arrive at the result.

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

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

If we observe the formula bar, we have not got the formula. Rather, we just got the result of the formula. Therefore, we must alter our code to apply the formula to cell C2. 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 the F5 key or manually. Then, this will apply the formula to cell C2.

–>> 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.

#### VBA Count Example #2

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

We have a few numerical and non-numerical values from A1 to A11. Therefore, we will apply the code below 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 get the result as 5.

However, when we closely look at the data from A1 to A11, we have numerical values in cells A1, A2, A3, A4, A5, A7, and A11. So, 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 cells A5 and A7, the values are stored as text, not as numbers. So the COUNT function has returned the result as only 5 because it treats those cells formatted or stored as text as non-numerical values and ignores the numerical count.

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

### Recommended Articles

This article has been a guide to VBA COUNT. Here, we learn how to use the VBA COUNT function to count numeric values in Excel, along with practical examples and a downloadable template. Below you can find some useful Excel VBA articles: –