Maruf Islam
Excel VBA is a strong tool to execute any operations. Count filled cells (ignoring blanks or empty cells) in Excel VBA is a common operation to execute. There are numerous VBA macro variants to count filled cells whether they are just filled or contain text, values, etc.
Let’s say, we have a dataset where sold Products are displayed. The cells contain texts, values and we want to count them. In this article, we’ll demonstrate the ways to count the filled cells in Excel VBA.
Download Excel Workbook
⧭ Opening Microsoft Visual Basic and Inserting Code in the Module
Before proceeding to demonstrate any methods, it’s necessary to know the ways to open and insert a Module in Microsoft Visual Basic in Excel.
🔄 Opening Microsoft Visual Basic: There are mainly 3 ways to open Microsoft Visual Basic window.
🔼 Using Keyboard Shortcuts
Press ALT+F11 altogether to open Microsoft Visual Basic window.
🔼 Using Developer Tab
In an Excel worksheet, Go to Developer Tab > Select Visual Basic. The Microsoft Visual Basic window appears.
🔼 Using Worksheet Tab
Go to any worksheet, Right-Click on it > Choose View Code (from the Context Menu).
🔄 Inserting a Module in Microsoft Visual Basic: There are 2 ways to insert a Module in Microsoft Visual Basic window,
🔼 After opening the Microsoft Visual Basic window, Select a Worksheet > Right-Click on it > Select Insert (from the Context Menu) > then Choose Module.
🔼 You can also do it by Selecting Insert (from the Toolbar) > then Choosing Module.
7 Easy Ways to Count Filled Cells in Excel Using VBA
Method 1: Use VBA Macro to Count Filled Cells from a Range
In the dataset, we have texts and values in the cells. We want to count all the cells that are not empty or blank. Also, we want to provide a range to count the filled cells.
Step 1: Open Microsoft Visual Basic then insert a Module using the instruction section. Paste the following macro in any Module.
Sub Count_cells_fromRange()
Dim ws As Worksheet
Set ws = Worksheets("NewYork")
ws.Range("H5") = Application.WorksheetFunction.CountA(ws.Range("B4:F14"))
End Sub
➤ in the code,
1 – start the macro procedure by declaring the Sub name. You can assign any name to the code.
2 – declare the variable as Worksheet.
3 – assign the variable to a specific worksheet.
4 – apply the COUNTA function to count filled cells from range B4:F14 then display it in the H5 cell.
Step 2: Press F5 to run the macro. After returning to the workbook, you see the total filled cell number as depicted in the below screenshot.
Read More: Excel Count Number of Cells in Range (6 Easy Ways)
Method 2: Count Cells Filled with Values Using VBA
We count filled cells irrespective of texts or values in Method 1. What if we want to count just cells containing only the values? As our dataset contains both text and values, we can choose one of the types in counting. However, in real-life scenarios, there can be blanks among cells. For rigid representation, we demonstrate this method with all filled cells.
Step 1: Repeat the instruction section to open and insert Module in Microsoft Visual Basic.
Sub Count_Cells_withValues
Range("H5") = Application.WorksheetFunction.Count(Range("B4:F14"))
End Sub
➤ From the above image, in the sections,
1 – begin the macro code declaring the VBA Macro Code’s Sub name.
2 – the COUNT function counts cells that contain values in range B4:F14 afterward displays it in cell H5.
Step 2: Hit F5 to run the macro and it inserts the total cell count containing values.
One revealing thing is that the range B4:F14 contains text cells (i.e., B4:F4, C5:C12, and E13), blank cells (i.e., B13:D13 and B14:F14), and the macro ignores it.
Read More: Count Cells That Are Not Blank in Excel (6 Useful Methods)
Method 3: Count Specific Filled Column Cells in Excel Using VBA
Our dataset has several columns. We can choose a particular column to count filled cells.
Step 1: Type the below macro code in the Module (inserted by following the instruction section).
Sub Count_FilledCells_inColumn()
Dim N As Integer
N = Worksheets("NewYork").Range("B:B").Cells.SpecialCells(xlCellTypeConstants).Count
MsgBox "There total number of filled cells in the column:" & N
End Sub
➤ The code’s sections,
1 – initiate the macro procedure declaring the Sub name.
2 – declare the N variable as Integer.
3 – assign the variable to a formula. The formula takes a worksheet (i.e., NewYork) and column (i.e., C column). The COUNT function counts all the filled cells in the column.
4 – a message box displays the count.
Step 2: Press F5 to run the macro. In a moment, the macro displays a message box and the filled cells count appears in the message box.
Read More: How to Count Cells with Specific Text in Excel (Both Case Sensitive and Insensitive)
Method 4: Formula to Count Cells Filled with Values
As we said earlier the dataset that we are using contains numeric values. And we want only the numeric values calculated by formula. In this method, we induce a formula in the macro code to count numeric entries.
Step 1: Write the subsequent macro in Microsoft Visual Basic Module.
Option Explicit
Sub Formula_Count_ValueCells
Range("H14").Formula = "=Count(H2:H12)"
End Sub
➤ From the above image, the code’s sections,
1 – take forward the macro by setting the Sub name.
2 – use the COUNT formula to count numeric cells from the B4:F13 range. Then, the macro displays the count in cell H5.
Step 2: In order to run the macro press F5. The macro displays the count in cell H5.
➤ Now, if we want to cross-check our findings by modifying the dataset. For instance, we insert 2 or 3 blank cells in the range and see what the macro returns.
We modify the dataset as depicted in the below picture.
➤ After executing the same macro, the macro returns 27 as the numeric value containing cells in the range.
After inspecting the above depiction, you can say the macro returns the correct counting. So existing blank cells are not an issue when we count filled cells with macros. Because macros that count filled cells ignore blank or empty cells.
Read More: How to Count Only Visible Cells in Excel (5 Tricks)
Method 5: Count Filled Cells from a Selection Using VBA
We can offer an option using macro code to select a random range whenever we count filled cells. In this case, we execute a loop to go through each cell within a range and count filled cells.
Step 1: Use the following macro code in any Module of any Microsoft Visual Basic window.
Sub Count_FilledCells_Selection()
Dim Rng As Range
Dim ActiveRng As Range
Dim Total As Long
On Error Resume Next
Text = "Select the range"
Set ActiveRng = Application.Selection
Set ActiveRng = Application.InputBox("Range", Text, ActiveRng.Address, Type:=8)
For Each Rng In ActiveRng
If Not IsEmpty(Rng.Value) Then
Total = Total + 1
End If
Next
MsgBox "The total filled cells number in the range:" &Total
End Sub
➤ The above image has code sections,
1 – take forward the macro by setting the Sub name.
2 – declare the variables as a Range and Long.
3 – assign the variables to the text and Input box function that requires range insertion through a message box command.
4 – create a loop to pass each cell within the range and add the non-empty cells.
5 – display the count through a message box.
Step 2: To run the macro press F5. A dialog box named Select the range appears. Click on OK.
➤ Clicking OK displays the count number in a message box as shown below.
➤ Let’s say, we want to cross-check the resultant value we got in this method previously. So, we empty some of the cells as depicted in the following picture.
➤ Afterward, we insert and run the same macro for this modified dataset, we get the result as follows.
We empty 6 cells as you can see from the dataset. And the macro returns 41 as result. Before emptying the cell, the result was 47, now (47-6) or 41. As a result, we can say the delivered count number is correct.
Read More: How to Count Non Blank Cells with Condition in Excel (6 Methods)
Method 6: Using Excel VBA to Count Cells Filled with Text from a Selection
In previous methods, we count cells filled with values. For this method, we consider counting cells filled with text.
Step 1: Select the entire range you want to count the text cells from similar to the picture below.
Step 2: To count the text paste the below macro in the Microsoft Visual Basic Module.
Sub Count_TextCells_Selection()
Dim Rng As Range
Dim i As Integer
For Each Rng In Selection
If Application.WorksheetFunction.IsText(Rng) Then
i = i + 1
End If
Next Rng
MsgBox "The total cells filled with text in the selection:" &i
End Sub
➤ The code is marked in parts,
1 – begin the macro by setting the Sub name.
2 – declaring the variables as Range and Integer.
3 – create a loop. Each cell in the range is tested for being Text or not then add the count if returns TRUE.
4 – display the count in a message box.
Step 2: Push the F5 key to run the macro. Afterward, the cell number that contains text appears in a message box.
Read More: Count Cells that Contain Specific Text in Excel
Method 7: Custom Function to Count Cells Filled with Color Using VBA
Suppose, we conditional format our dataset. We apply different criteria to conditionally color each column entry. For example, we impose >50 for the Quantity column, >2 for the Unit Price column, >100 for the Total Sale column. The color we use also serves as a criterion. Now, we can create a custom function using the VBA macro to be used in the worksheet.
So, in a nutshell, the dataset looks something like the below picture.
Step 1: As we want to create a custom function, use the following macro in any Module.
Function CountCellColored(Rng As Range, criteria As range) As Long
Dim data As range
Dim cell As Long
cell = criteria.Interior.ColorIndex
For Each data In Rng
If data.Interior.ColorIndex = cell Then
CountCellColorED = CountCellColored + 1
End If
Next data
End Function
➤ The macro code is numbered in parts,
1 – begin the macro by setting the Function name CountCellColored, Function syntax (i.e., range, criteria).
2 – declaring the variables as Range and Long.
3 – assign the cell variable to a criteria Color Index function.
4 – create a loop to test whether each cell is conditionally colored or not. Add cells in case the test returns TRUE.
Step 2: After inserting the macro, return to the worksheet. In the worksheet type the following formula in cell I5.
=CountCellColored(B4:F13,H5)CountCellColored is the custom function. B4:F13 is the range and H5 is the criteria.
Step 3: Hit ENTER to display the colored cell count.
If you want to cross-check the count just count the colored cells in the dataset. You’ll find the formula returned values are correct.
Read More: How to Count Blank Cells in Excel with Condition (3 Methods)
Conclusion
In this article, we use multiple variants of macro codes to count filled cells in Excel VBA. However, we try to count the filled cells with different entry types (i.e., text or value). Hope these above-described methods provide you with your desired one. Comment, if you have further inquiries or have anything to add.
