Apply count function in a cell in VBA Excel |

Apply count function in a cell in VBA Excel


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


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.


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



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.


Post you may like

Apply Countif function in vba excel