Number of Cells/Rows/Columns With Formula
A few weeks ago I received this question from someone called LS:
Hi
I want to write a macro that counts the following
- Number of cells with formula
- Number of Rows with formula
- Number of Columns with formula
Please let me know if there is a way of doing it in Excel VBA
To find out the number of cells, rows, or columns with formula in active Excel worksheet is quite simple.
In my previous post, we already have the Excel VBA code on how to select all cells with formula, so we only need to tweak this macro a little bit and get the results that we looking for.
Sub NumberOfCellsWFormula()
'select all cells with formula
Cells.SpecialCells(xlCellTypeFormulas).Select
'number of cells with formula
Debug.Print Selection.Count
'number of rows with formula
Debug.Print Selection.Rows.Count
'number of columns with formula
Debug.Print Selection.Columns.Count
End Sub
The Excel VBA procedure above will simply print the number of cells, rows, and columns of current active Excel Worksheet that containing any formula in it. Please be sure to run it from Excel VBA windows with Immediate Window turn on (View ยป Immediate Window or CTRL+G).
In short, we only need to use Range properties Count, Rows.Count, and Columns.Count to get the number of selected cells, rows or columns.
FIN.
Related Entries
External Resources
- Microsoft Excel 2003/2007 Video Tutorials
Step-by-step video guide to mastering Charts, PivotTable, Data Analysis and Macro programming in Microsoft Excel in 5 hours.
- 101 Secrets of Microsoft Excel
Discover 101 of Excels little-known secrets that have been hiding right under your nose.
Tagged with: cells, columns, excel cells, excel formula, rows, vba macro, worksheet cells
JP
Isn’t it strange that this works:
Cells.SpecialCells(xlCellTypeFormulas).Select
Selection.Count
but this doesn’t?
Cells.SpecialCells(xlCellTypeFormulas).Count