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