Count Function in Visual Basic (VBA)
A free video tutorial from Daniel Strong
Excel Instructor/Programmer
4.4 instructor rating •
10 courses •
119,043 students
Lecture description
Learn to use the Count function using Excel VBA and CommandButtons! We'll use a small data set to count how many cells are numeric using Application.WorksheetFunction.Count, or total number of cells that are not blank using CountA function. The cool thing about this approach is there's no formula to be overwritten or updated, just a static number thanks to Visual Basic in Excel!
Learn more from the full course
The Ultimate Excel Programmer CourseLearn Excel VBA from Scratch with Dan Strong, Bestselling Excel Expert with Over 180K Students Worldwide!
12:31:31 of on-demand video • Updated August 2020
- Automate and Customize data entry forms
- Choose the right Loop for each task
- Master the CELLS and RANGE objects in multiple scenarios
- Create multiple Variable styles to match your need
- Customize your VBA Editor and Understand all the Toolbars and options
- Debug and Troubleshoot code like a boss!
- Record, Modify or Write Macros from scratch
- Make Custom Formulas/Functions on the fly
- Breeze through IF THEN statements and conquer all the Logical Operators
- Batch out inter-active MessageBoxes, InputBoxes and give users CHOICES!
- Generate Basic Reports that can be printed
- Add filters to report menus to narrow the records
- Take control of forms, Buttons, Drop-down menus, Checkboxes and option buttons AND so much more. . .
- Trigger code from a number of different methods - from Clicking on a cell - to De-Selecting a worksheet.
- Set up Special Commands when a workbook: Opens or closes, is selected, any cell or certain cells are selected, right before printing, etc. . .
- Manipulate Userforms for data entry, report generation, editing tables/databases - ALL within your control. Restrict the flow of data OR make the Userform(s) responsive, calculating, INTUITIVE.
- Streamline your work and the work of others.
- Put Excel ON AUTOPILOT. . .
English
Now we're going to show you the Count() function which is basically going to count everything in column C. Again we could use a more dynamic range if we felt like it. So we're going to do the same thing, we're going to go into design mode. And in fact I'm going to go into this and I'm just going to copy what we've already done because you guessed it, the count function is going to be really similar. Now if you don't know what the Count() function is - the SUM function is taking all these numbers and adding them together 20, 45, 300, 40 - that's going to equal 405, but the Count function will actually just give you the number of cells. So one two three four. So let's try this and then we may have to tweak it because look there's actually another cell to be counted. If we're taking C:C (the entire column). So let's take a look and we'll start our adventure here we go. I'm going to paste. So we want.. not F2 but F3. So let's tweak that a little bit. F3 is going to be equal to the Application.WorksheetFunction And in this case we're going to say .Count So let's see if there is a .Count Ah yes there is so dot count range. C:C - let's see what that does. Let's go ahead and click it and see what our result is at first. So we're going to get 4. OK. Now let's see what happens if we change it to CountA() . So there's a CountA() function in Excel as well. And that would give us 5. So the Count() function is actually just counting. Anything that's a number and the CountA() is counting anything that's not blank in this instance. So I would probably want to change this back to Count() and not CountA() But there's different uses for both functions. So play around with that and make sure you get which everyone tests well for what you're looking for . So the cool thing about this is once again click a couple of buttons or run your procedure and everything is there. And if it's erased it doesn't matter because you can run your procedure again. That's the Count() Function!