• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

VBA COUNTA

Home » VBA » VBA Statistical Functions » VBA COUNTA

By Jyoti Singh Leave a Comment

VBA Counta Function

COUNTA Worksheet Function in Excel VBA

In our earlier article “Excel COUNTA” we have seen how to use the COUNT function to count the numerical values from the range of values. How about counting all the values in the range of cells? Yes, we can count that as well. To count all the cell values in the range of cells we need to use the formula “COUNTA” in excel VBA. In this article, we will show you how to use the COUNTA function in VBA to count all the cell values in the supplied range.

Examples of COUNTA Function in VBA

One of the truth is “COUNTA” function is not a VBA function. I know your question is if it is not a VBA function then how do we use it? Nothing worry even though it is not a VBA function still we can use it under the worksheet function class in VBA coding.

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

Ok, let’s write the code to apply the excel VBA COUNTA.

Step 1: Create a subprocedure name.

Step 2: Now first decide where we are going to store the result of the VBA COUNTA function. In this example, I want to store the result in cell C2. So my code will be Range(“C2”).Value.

Code:

Sub Counta_Example1()

 Range("C2").Value =

End Sub

VBA Counta Example 1

Step 3: In cell C2 we need the value of the VBA COUNTA function. So to apply the excel VBA COUNTA function lets first apply worksheet function class.

Code:

Sub Counta_Example1()

 Range("C2").Value = Work

End Sub

VBA Counta Example 1-1

Step 4: After applying the worksheet function class select the formula COUNTA by putting a dot.

Code:

Sub Counta_Example1()

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

End Sub

VBA Counta Example 1-2

Step 5: Now we need to supply the range of cells to be counted. In this example, we need to count the range of cells from A1 to A11. So supply the cells using the VBA RANGE object.

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

View Course

Related Courses

Code:

Sub Counta_Example1()

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

End Sub

VBA Counta Example 1-3

Ok, let’s run the code to get the result in cell C2.

VBA Counta Example 1-4

So, the same result returned by VBA COUNTA as well.

Like this, we can use COUNTA to count the non-empty or non-blank cells from the supplied range.

Coding with Variables

VBA variables are the key to building a project. Now for the same data, we can declare VBA variables and arrive at the result.

For an example look at the below code.

Code:

Sub Counta_Example2()

 Dim CountaRange As Range
 Dim CountaResultCell As Range

 Set CountaRange = Range("A1:A11")
 Set CountaResultCell = Range("C2")

 CountaResultCell = WorksheetFunction.CountA(CountaRange)

End Sub

Let’s explain the above code now.

First I have declared the variable “CountaRange” as a range to reference the range of values.

Dim CountaRange As Range

Next, I have set the reference as Range A1 to A11.

Set CountaRange = Range("A1:A11")

The second variable is to reference the COUNTA result cell.

Dim CountaResultCell As Range

For this variable, I have set the cell as C2.

Set CountaResultCell = Range("C2")

Now, as usual, I have applied the COUNTA function using variables instead of hardcoded ranges. Now, look at the old code and this VBA code.

Code 1:

Code 1

Code 2:

Code 2

In code 1 we have Range C2, in Code 2 we have the variable name “CountaResultCell”. Here the variable “CountaResultCell” set reference as C2 cell. So this variable is C2 cell now.

In code 1 COUNTA function range is A1 to A11, in code 2 it is variable called “CountaRange”. This variable holds a reference to the range A1 to A11.

This is what is the difference between old code and code with variables.

So, the COUNTA function helps us to count all the non-empty cells from the supplied range irrespective of the data it has.

Recommended Articles

This has been a guide to VBA COUNTA. Here we learn how to use COUNTA Worksheet Function in Excel VBA along with examples and download excel template. Below are some useful excel articles related to VBA –

  • Exit Subprocedure in Excel VBA
  • Declare One Dimensional Array in VBA
  • Excel VBA If Else
  • Save As Function in VBA
  • Range Cells in VBA
  • Val Functions in VBA
  • COUNTIF Function in VBA
  • Count Function in VBA
4 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 >>

Filed Under: VBA, VBA Statistical Functions

Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. 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

Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

WallStreetMojo

Free Excel Course

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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

WallStreetMojo

Download VBA COUNTA Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

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