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.
📁 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.
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.
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.
- 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
🔨 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.
- Lastly, we will have the cell count in cell D12.
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.
- 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
🔨 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.
- Next, Macros named box will appear. Select Counting_Filled_Cells_with_Values and then Run.
- Lastly, we will have the cell count in cell D12.
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.
- 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
🔨 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.
- Next, Macros named box will appear. Select Counting_Filled_Cells_in_Column and then Run.
- Lastly, we will have the cell count shown in the message box.
📕 Read More: 7 Easy Ways to Count Number of Cells with Dates in Excel
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.
- 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.
- Next, Macros named box will appear. Select Formula_Counting_Filled_Cells and then Run.
- At the end, we will have the cell count in cell D12.
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.
- 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.
- Next, Macros named box will appear. Select Counting_Filled_Cells_From_Selection and then Run.
- Enter range C6:D10 in the box Insert Range.
- Lastly, we will have the cell count in cell D12.
📝 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
- 3 Easy Ways to Count Odd and Even Numbers in Excel
- Count Blank Cells with Condition in Excel (3 Ways)