How to Count Filled Cells in Excel Using VBA (7 Methods)

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.

Dataset-Count 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.

developer tab-Count Filled Cells in Excel Using VBA

🔼 Using Worksheet Tab

Go to any worksheet, Right-Click on it > Choose View Code (from the Context Menu).

Worksheet option-Count Filled Cells in Excel Using VBA

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

Select worksheet-Count Filled Cells in Excel Using VBA

🔼 You can also do it by Selecting Insert (from the Toolbar) > then Choosing Module.

insert module-Count Filled Cells in Excel Using VBA


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

method 1-Count Filled Cells in Excel using VBA

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

method 1 result

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.

Method 2-Count Filled Cells in Excel using VBA

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

macro

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

Method 2 result

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

method 3-Count Filled Cells in Excel using VBA

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

Result

Read More: How to Count Cells with Specific Text in Excel (Both Case Sensitive and Insensitive)


Similar Readings:


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

Method 4 macro-Count Filled Cells in Excel using VBA

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

Result

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

Modified dataset

➤ After executing the same macro, the macro returns 27 as the numeric value containing cells in the range.

Cross Check

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

Method 5 Macro-Count Filled Cells in Excel using VBA

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

Select range

➤ Clicking OK displays the count number in a message box as shown below.

Result

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

Modified dataset

➤ Afterward, we insert and run the same macro for this modified dataset, we get the result as follows.

Result

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.

Selection-Count Filled Cells in Excel using VBA

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

Macro

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

Result

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.

Dataset-Count Filled Cells in Excel using VBA

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

Macro

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

Formula insertion

Step 3: Hit ENTER to display the colored cell count.

Result-Count Filled Cells in Excel Using VBA

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.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo