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

  • How To Hide an Excel Worksheet

    Some time we want to hide a certain Excel Worksheet from view, and it’s a common practice to use select the Worksheet, go to menu Format > Sheet > Hide. Using the method explained above is right, but unfortunately, others people can easily unhide the Worksheet using the same method, only this time, instead of selecting...

  • Select All Cells With Formula In It

    Let say we want to distinguish between ordinary cells and cells with formula in it, so we give a special background color to the cells with formula. For doing it manually is time consuming, especially on Excel Worksheet with large of cells with formula in it (usually a very complex worksheet). The following vba macro code will...

  • Convert Excel Column Number into Column Name

    Update: Sorter version of the VBA function:Function Number2Char(c As Integer) As String Number2Char = Split(Cells(1, c).Address, "$")(1) End FunctionCourtesy of The Plaid Cow. ORFunction Number2Char(ByVal vNumber)Dim addr As String If vNumber 256 Then Exit Functionaddr = Range("A1").Offset(0, vNumber - 1).Address Number2Char...

  • How To Find the Last Row That Contain Data in Excel?

    UPDATE June 13, 2008: Another alternative way to find the last row with data :Function LastRow() As LongDim ix As Long ix = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count LastRow = ixEnd FunctionI did a simple test, and the function will return the correct last row position...

  • How to ceiling a decimal number in Excel?

    Ceiling, or changing a specific decimal number into become its upper number, for example if we ceiling 3.25 we will got 4, or if we ceiling 2.75 then we will got 3 as a result. To get a result like that in Microsoft Excel, we can simply use this very simple but neat Excel VBA functionFunction...

Valuable Resources

If you found this page useful, please consider bookmark it using social media or add a link to this page.

  • Print
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • connotea
  • Diigo
  • email
  • Fark
  • Identi.ca
  • Live
  • MisterWong
  • MySpace
  • Netvibes
  • NewsVine
  • PDF
  • Ping.fm
  • Propeller
  • Reddit
  • Simpy
  • StumbleUpon
  • Technorati
  • Twitter
  • Wikio
  • Yahoo! Bookmarks

Incoming search engine terms: vba count rows, excel macro count the no of rows with text, excel vba count rows, excel macro count rows, Excel VBA selection count, excel vba number of rows, vba count rows with data, excel macro count number rows, Excel VBA how many cells in a column with data, VBA number of rows, number of cells selected in vba excel, number of columns and number of rows in a worksheet, ms macro count rows in a range, number of columns in excel, number of columns in selected range vba

«
»