EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Statistical Functions VBA Count
Secondary Sidebar
VBA Statistical Functions
  • VBA Statistical
    • VBA Count
    • VBA COUNTA
    • VBA Counter
    • VBA Max

VBA Count

By Ashwani JaiswalAshwani Jaiswal

Excel VBA Count

VBA Count Function

In Excel, we use the count function to count the number of cells which contains numbers. Same can be done in VBA as well. In VBA, we can use the same function Count to get how many numbers of cells contain numbers. It only counts the cell with numbers. Values other than numbers cannot be counted.

Syntax of Count in Excel VBA

The syntax for the VBA Count function in excel is as follows:

Syntax

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to Use VBA Count in Excel?

We will learn how to use a VBA Count Function with few examples in excel.

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

Example #1 – VBA Count

For implementing this we have a list of some data in column A. This list contains numbers and texts as shown below. Now we with the help of Count function in VBA we will see, how many cells are having numbers. For this, we have identified a cell at A8 position, where we will see the output of Count Function through VBA.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,389 ratings)

numbers and texts

Step 1: For this, we require a module. Go to Insert menu tab and click on Module option as shown below from the list.

Module

Step 2: After that, we will get the blank window of Module. Now in that write the subcategory of VBA Count. Or choose any other name as per your choice.

Code:

Sub VBACount()

End Sub

Subcategory

Step 3: Select the range of the cell where we want to apply Count function. Here, our output cell is A8 as defined above. So we have selected it as our Range.

Code:

Sub VBACount()

Range("A8").

End Sub

VBA CE 1.3

Step 4: Now get the Value command, and it allows us to add the value in it.

Code:

Sub VBACount()

Range("A8").Value =

End Sub

VBA CE 1.4

Step 5: Now with the help of Count Function, select the range of the cells from which we want to get the count of a number of cells which contains Numbers only. Here, we have selected the range of cells from A1 to A6.

Code:

Sub VBACount()

Range("A8").Value = "=Count(A1:A6)"

End Sub

VBA CE 1.5

Ste 6: Once done then compile the code and run by clicking play button. As we can see below, the count of cells containing numbers is coming as 3. Which means the Count function in VBA has given the count of cells with numbers which are from cell A1 to A3.

VBA CE 1.5

Example #2 – VBA Count

In a similar way, we have another set of data. But this data has some dates, number with text along with numbers and text as shown below. We have fixed a cells C12 where we will see the output of Count function through VBA.

VBA CE 2.1

Now we will apply the Count function and see if this can Count date and number-text cells or not. We can choose to write the new code again or we can refer the same code which we have seen in example-1 and just change the reference cells.

Step 1: Go to Insert menu tab and click on Module option as shown below from the list.

Code:

Sub VBACount2()

End Sub

VBA CE 2.2

Step 2: Select the range of cell where we want to see the output. Here that cell is C12.

Code:

Sub VBACount2()

Range("C12").Value =

End Sub

VBA CE 2.2

Step 3: Now use the count function in inverted commas in select the range of those cells which we need to count. Here that range is from cell C1 to C10.

Code:

Sub VBACount2()

Range("C12").Value = "=Count(C1:C10)"

End Sub

VBA CE 2.3

Step 4: Now run the above code.

We will see the Count function has returned the count of cells as 6 as shown below. Which means, count function can count cells with Date as well. Here, the values which are highlighted as bold are those values which just got counted through Count function in VBA.

VBA CE 2.4

Example #3 – VBA Count

There is another way to use Count Function in VBA. This method involves using Active Cells of the sheet. Here we will use the same data which we have seen in example-1.

Active Cells

Step 1: Open a new module and create the subcategory in the name of VBA Count as shown below.

Code:

Sub VBACount3()

End Sub

Subcategory

Step 2: First, insert the ActiveCell function in VBA. This will help in selecting the range of cells.

Code:

Sub VBACount3()

ActiveCell.

End Sub

ActiveCell Function

Step 3: Now with the function Formula, select the row number and column number which we want to insert in Count function. Here our reference Row is starting from 1 and Column is also 1.

Code:

Sub VBACount3()

ActiveCell.FormulaR1C1 =

End Sub

Function Formula

Step 4: Now insert the Count function under inverted commas as shown below.

Code:

Sub VBACount3()

ActiveCell.FormulaR1C1 = "=COUNT()"

End Sub

Count function

Step 5: Select the range of the cells from the point where we are applying the Count function. As we are going up from A8 to A1 so row count will be “-7” and column is first to nothing is mentioned to the row count “-2” from the starting point which is cell A8.

Code:

Sub VBACount3()

ActiveCell.FormulaR1C1 = "=COUNT(R[-7]C:R[-2]C)"

End Sub

applying the Count function

Step 6: Now select the range of cell where we want to see the output. Here at this range cell A8, we will see the cursor as well.

Code:

Sub VBACount3()

ActiveCell.FormulaR1C1 = "=COUNT(R[-7]C:R[-2]C)"
Range("B8").Select

End Sub

Range of Cells

Step 7: Now run the code. We will see, the count function has returned the same count of number as 3 which we got in example-1.

Count of Number

Pros of VBA Count

  1. It is as easy as applying Count Function in excel.
  2. This is one of the easiest function that could be automated through VBA.
  3. If the process Count is repeating multiple times then automating the same with the help of Count function in VBA is quite a time saving and effort minimizing way.

Things to Remember

  • While applying the count function in VBA, always quote the function name in inverted commas.
  • As we use Count in Excel, the same way is also seen while applying Count Function in VBA.
  • The process of applying VBA Count can be done by recoding a macro as well.
  • Always save the written code in VBA in Macro enable excel file format to avoid losing code.

Recommended Articles

This is a guide to VBA Count. Here we discuss how to use Excel VBA Count Function along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Copy Paste
  2. VBA Month
  3. VBA Subscript out of Range
  4. VBA Selecting Range
Popular Course in this category
VBA Training (4 Courses, 12+ Projects)
  4 Online Courses |  13 Hands-on Projects |  50+ Hours |  Verifiable Certificate of Completion
4.7
Price

View Course
2 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more