5 Examples to Count Filled Cells Using VBA in Excel

We can count cells in Excel by using various functions, features, and formulas. However, all these processes may have too many steps. As a result, we will use VBA code to count filled cells in Excel. So, in this article, we will show you 5 simple VBA codes to count filled cells in Excel VBA.

Count filled cells in excel VBA


📁 Download Excel File

You can download the Excel file used for the demonstration from the link below.


Learn to Count Filled Cells Using VBA in Excel with These 5 Examples

First, let us become acquainted with our dataset, which will be used as an example throughout the article. For a store, we have a sales record that includes the product name, selling date, and price. Now we want to count the number of filled cells in a range, a selection, a specific column, and so on. As a result, we will use this dataset to demonstrate 5 simple functions in Excel VBA for counting filled cells.

Dataset for counting filled cells in excel VBA

Ensure you have the Developer tab in the ribbon before applying codes. If you do not have the Developer tab, click here to see how to enable the Developer tab on your ribbon.


Approach 1

1. For a Range

This method will demonstrate how to count filled cells in Excel using Microsoft Visual Basic Application (VBA). The code in this method counts the filled cells for a defined range.

Now, let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First, go to the Sheet tab (at the bottom of the Excel interface), right-click on the current sheet, and select View Code from the options.

Selecting view code by right-clicking Sheet tab

  • Then, a new window will appear. Insert the code there.
Sub Counting_Filled_Cells_from_Range()

Dim WSheet As Worksheet
Set WSheet = Worksheets("For a Range")
WSheet.Range("D12") = Application.WorksheetFunction.CountA(WSheet.Range("B5:D10"))

End Sub

Inserting code to count filled cells from a range

🔨 Code Breakdown

👉 Here, Counting_Filled_Cells_from_Range is the name of the Sub.

👉 Then, we defined WSheet as Worksheet.

👉 After that, we set the worksheet For a Range as WSheets.

👉 Next, Application.WorksheetFunction.CountA(WSheet.Range(“B5:D10”)) works in the range B5:D10 and returns the cell count in cell D12.

👉 Lastly, End Sub end the sub.

  • After that, click on Run in the toolbar or press F5.
  • Next, Macros named box will appear. Select Counting_Filled_Cells_from_Range and then Run.

Running the code by selecting Macros

  • Lastly, we will have the cell count in cell D12.

Outcome of count filled cells in a range

Approach 2

2. Cells Containing Value

Using Microsoft Visual Basic Application (VBA), this method will demonstrate how to count filled cells in Excel that contain a value. The code in this method counts the filled cells that contain a value, whereas the previous method counts all type data filled cells.

Now, let us go through the steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First, go to the Sheet tab (at the bottom of the Excel interface), right-click on the current sheet, and select View Code from the options.

Selecting view code by right-clicking on With Value Sheet

  • Then, a new window will appear. Insert the code there.
Sub Counting_Filled_Cells_with_Values()

Range("D12") = Application.WorksheetFunction.Count(Range("B5:D10"))

End Sub

Inserting code to count filled cells with values

🔨 Code Breakdown

👉 Here, Counting_Filled_Cells_with_Values is the name of the Sub.

👉 Next, Application.WorksheetFunction.CountA(WSheet.Range(“B5:D10”)) works in the range B5:D10 and returns the cell count in cell D12.

👉 Lastly, End Sub end the sub.

  • After that, click on Run in the toolbar or press F5.

Running the code from toolbar

  • Next, Macros named box will appear. Select Counting_Filled_Cells_with_Values and then Run.

Selecting the Macro Counting_Filled_Cells_with_Values

  • Lastly, we will have the cell count in cell D12.

Outcome of count filled cells containing value

Approach 3

3. Count Cells in Specific Column

This approach will show you how to count filled cells in Excel that contain a specific value using the Microsoft Visual Basic Application (VBA). In contrast to the previous two methods, which counted all filled cells of any data, this method’s code only counts the filled cells for a specific value.

Let’s follow the steps now.

⬇️⬇️ STEPS ⬇️⬇️

  • First, on the Sheet tab (at the bottom of the Excel interface) right-click the active sheet and choose View Code from the menu that appears.

Selecting view code by right-clicking on Specific Value Sheet

  • Then a new window will appear. Insert the code there.
Sub Counting_Filled_Cells_in_Column()

Dim I As Integer
I = Worksheets("Specific value").Range("D:D").Cells.SpecialCells(xlCellTypeConstants).Count
MsgBox "The total number of filled cells in the column:" & I

End Sub

Inserting code to count filled cells in Column

🔨 Code Breakdown

👉 Here, Counting_Filled_Cells_in_Column is the name of the Sub.

👉 Then, declared I as an Integer.

👉 Next, Range(“D:D”).Cells.SpecialCells(xlCellTypeConstants).Count counts cells filled in column D.

👉 And returns the cell count in I integer.

👉 Lastly, MsgBox “The total number of filled cells in the column:” & I shows a message box where it shows the filled cell count.

  • After that, click on Run in the toolbar or press F5.

Running the code from toolbar

  • Next, Macros named box will appear. Select Counting_Filled_Cells_in_Column and then Run.

Selecting the Macro Counting_Filled_Cells_in_Column

  • Lastly, we will have the cell count shown in the message box.

Outcome of count filled cells in specific column

📕 Read More: 7 Easy Ways to Count Number of Cells with Dates in Excel

Approach 4

4. Utilizing Formula in VBA

This technique shows you how to count filled cells using a formula in Excel using VBA. The code for this method counts the filled cells applying a formula directly, as opposed to the code for the previous methods, which applied commands of VBA.

Let’s proceed with the steps now.

⬇️⬇️ STEPS ⬇️⬇️

  • To begin, go to the Sheet tab (at the bottom of the Excel interface), right-click on the current sheet, and choose View Code from the menu.

Selecting view code by right-clicking on Formula Sheet

  • After that, a new window will open. Type the following code there.
Option Explicit

Sub Formula_Counting_Filled_Cells()
Range("D12").Formula = "=Count(D6:D10)"

End Sub

🔨 Code Breakdown

👉 Here, Formula_Counting_Filled_Cells() is the name of the Sub.

👉 Next, Range(“D12”).Formula = “=Count(D6:D10)” is the formula that counts filled cells in range D6:D10.

👉 Lastly, End Sub end the sub.

  • After that, click on Run in the toolbar or press F5.

Running the code from toolbar

  • Next, Macros named box will appear. Select Formula_Counting_Filled_Cells and then Run.

Selecting the macro Formula_Counting_Filled_Cells

  • At the end, we will have the cell count in cell D12.

Outcome of count filled cells using a formula

Approach 5

5. For a Specific Selection

This approach will show you how to count filled cells for a selection in Excel using VBA. Instead of counting all filled cells in a range or for a particular column, the code in this method only counts the selected cells.

Let’s perform the procedures now.

⬇️⬇️ STEPS ⬇️⬇️

  • To begin, navigate to the Sheet tab (near the bottom of the Excel interface), right-click on the current sheet, and select View Code from the menu.

Selecting view code by right-clicking on For a selection Sheet

  • Then, a new window will appear. Insert the code there.
Sub Counting_Filled_Cells_From_Selection()

Dim R As Range
Dim AR As Range
Dim T As Long

On Error Resume Next

Text = "Select cell range"
Set AR = Application.Selection
Set AR = Application.InputBox("Insert Range", Text, AR.Address, Type:=8)

For Each R In AR
If Not IsEmpty(R.Value) Then
Total = Total + 1
End If
Next

MsgBox "The count of filled cells in the range:" & Total

End Sub

🔨 Code Breakdown

👉 Here,  Counting_Filled_Cells_From_Selection is the name of the Sub.

👉 Next, we declared R and AR as Range and T as Long.

👉 On Error Resume Next , this tells the code to continue from next cell if it finds any error while running.

👉 Text = “Select cell range”

Set AR = Application.Selection

Set AR = Application.InputBox(“Insert Range”, Text, AR.Address, Type:=8)

Input box functions that call for range insertion in the text must have variables assigned to them via a message box command.

👉 For Each R In AR

If Not IsEmpty(R.Value) Then

Total = Total + 1

End If

Next

A For loop passes through each cell in the range and adds the non-empty cells.

👉 Finally, End Sub end the sub.

  • After that, click on Run in the toolbar or press F5.

Running the code from toolbar

  • Next, Macros named box will appear. Select Counting_Filled_Cells_From_Selection and then Run.

Selecting the macro Counting_Filled_Cells_From_Selection

  • Enter range C6:D10 in the box Insert Range.

Inserting range

  • Lastly, we will have the cell count in cell D12.

Outcome of count filled cells for a selection


📝 Takeaways from This Article

📌  This article demonstrated 5 VBA codes to count filled cells in Excel.

📌  In the first method, we showed a code that counts filled cells in a range in Excel.

📌  Then, we demonstrated how to count filled cells containing values in Excel.

📌  Next, we showed a code that counts filled cells in a specific column in Excel.

📌  After that, we demonstrated how to count filled cells using the formula in VBA code.

📌  Finally, we counted cells for a selection of cells in Excel VBA.


Conclusion

This article has demonstrated 5 VBA codes to count filled cells in Excel. All these macros are incredibly effective and simple to use. We hope this article helps you. Please leave a remark if you have any queries. The author will do their best to find an appropriate answer.

For more guides like this, visit Excelden.com.


Related Articles

(Visited 68 times, 1 visits today)
Priti

Priti

Hello! Welcome to my Profile. I am Priti. I'm now working and researching Microsoft Excel, and I'll share articles on it here. I am passionate about problem-solving, and I hope these articles will help you. Please leave a remark if you have any queries, and I will do my best to find an appropriate answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo