Apply count function in a cell in VBA Excel |debugvba.com

Apply count function in a cell in VBA Excel

Objective

To apply the count formula in a cell using VBA excel.

Approach

Here we have applied Count formula in cell C1. We have used the formula property of range object. Using this approach we can count the number of values in selected range.

Code

Sub CountFormula()
Dim MyRange As String

MyRange = "A2:B6"

'Apply count formula
Range("C1").Formula = "=count( " & MyRange & ")"


End Sub

 

In the above shown code we can see that we wanted to count the cells which had data, and our selected range was  A2: B6. We have applied the count formula in cell C1. Cell A2 to A6 had data in 5 cells, while cell B2 to B6 was blank, so count function will show result as only 5. Count function does not consider blank cells while calculating the result.That is why column B cells were not included in the result .We can see the result in cell C1, as shown in the image below.

 

Apply count function in a cell in VBA Excel

 

Note

1.Code is made dynamic by adding MyRange variable in formula.
2.Formula can be seen in the formula bar.
3.We can apply other formulas using similar approach.

Reference

https://docs.microsoft.com/en-us/office/vba/api/excel.range.formula

Post you may like

Apply Countif function in vba excel