WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Count

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

VBA Count

Examples of VBA Count Function

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

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

VBA Count Example #1

Let 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

VBA Count Example 1

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

VBA Count Example 1-1

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

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Code:

Sub Count_Example1()

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

End Sub

VBA Count Example 1-2

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

Code:

Sub Count_Example1()

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

End Sub

VBA Count Example 1-3

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

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

VBA Count Example 1-4

As usual, we got the result as 3 like how we got 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. 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

VBACount Example 1-5

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

VBA Count Example 1-6

VBA Count Example #2

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

VBACount Example 2

We have a few numerical & non-numerical values from A1 to A11. Now I will apply the 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

Example 2-1

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

VBA Count Example 2-2

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 are formatted or stored as text as non-numerical values and ignores from 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 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
  • What is VBA Split Function in Excel?
  • End in VBA 
  • VBA ArrayList
1 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Count Function Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More