Count cells that contains formula

Question

I use the following code to count the number of cells:

Range(Range("K2"), Range("K2").End(xlDown)).Count

However, the cells originally contain the "=IF" formula so the codes return an incorrect number of cells. (It returns 4xxx instead of the "60" that I am looking for.)

Alternatively, I have tried using

Cells(Rows.Count, "K").End(xlUp).Row

Although it returns a much smaller number (i.e. 75), it also includes some of the empty cells in row (61-65). How can I get the number that I am looking for (i.e. 60 before those empty cells), thanks.

use the `Find()` Method on column K from here: https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba — Scott Craner, May 04 '20 at 21:58

VBasic2008 · Answer 1 · 2020-05-04T23:48:10.727

xlValues of LookIn Saves the Day

A One-Liner (Not recommended)

NumberOfRows = Range(Cells(2, "K"), Columns("K").Find(What:="", _
  After:=Cells(1, "K"), LookIn:=xlValues).Offset(-1)).Rows.Count

or referring to the code of the following 2 subs:

NumberOfRows = Range(Cells(HeaderRow + 1, Col), Columns(Col).Find(What:="", _
  After:=Cells(HeaderRow, Col), LookIn:=xlValues).Offset(-1)).Rows.Count

A More Detailed Explanation

Sub CountRows()

    Const Col As Variant = "K"    ' Source Column Number/Letter
    Const HeaderRow As Long = 1   ' Header Row
    Dim rng As Range              ' Last Cell, Target Range
    Dim NumberOfRows As Long      ' Number of Rows

    ' Define Last Cell.
    Set rng = Columns(Col).Find(What:="", After:=Cells(HeaderRow, Col), _
      LookIn:=xlValues).Offset(-1)
    ' Define Target Range and write its number of rows to Number of Rows.
    ' Display the result in the Immediate window.
    If Not rng Is Nothing Then
        Set rng = Range(Cells(HeaderRow + 1, Col), rng)
        NumberOfRows = rng.Rows.Count
        Debug.Print "Number of Rows = " & NumberOfRows
    Else
        MsgBox "No empty cells" ' Highly unlikely.
    End If

End Sub

An Even More Detailed Explanation

Sub CountRowsStudy()

    Const Col As Variant = "K"    ' Source Column Number/Letter
    Const HeaderRow As Long = 1   ' Header Row
    Dim rng As Range              ' First Blank Cell Range, Target Range
    Dim NumberOfRows As Long      ' Number of Rows

Debug.Print Columns(Col).Address
Debug.Print Cells(HeaderRow, Col).Address

    ' 'Columns(Col)' means the whole column whatever Col is. In this case
    ' Col is declared as Variant so either "K" or 11 can be used.
    ' 'Cells(HeaderRow, Col)' means at the intersection of Header Row
    ' and Source Column, i.e. range "K1". The 'After' argument when used
    ' with the omitted default parameter ('xlNext') of the 'SearchDirection'
    ' argument, starts the search one cell after the cell used in the
    ' 'After' argument, i.e. after "K1" which will then first 'look' into "K2"
    ' which also might be empty. The xlValues parameter of the LookIn argument
    ' will ensure to stop at the first empty cell whether it is blank
    ' or has a formula evaluating to "" in it.

    ' Define Last Cell i.e. Last Non-Empty Cell Range before
    ' the First Empty Cell Range i.e. find the first blank cell or
    ' the cell containing a formula evaluating to "" and use offset to go
    ' up one cell because you don't want the found empty cell to be counted.
    Set rng = Columns(Col).Find(What:="", After:=Cells(HeaderRow, Col), _
      LookIn:=xlValues).Offset(-1)
Debug.Print rng.Address
    ' Check if the column is not empty (full).
    If Not rng Is Nothing Then
        ' Define Target Range. It's starting from the same cell as the "After"
        ' argument, only one row down (we don't want the headers included),
        ' and it ends with the previously defined Last Non-Empty Cell Range.
        Set rng = Range(Cells(HeaderRow + 1, Col), rng)
Debug.Print rng.Address
        ' Count the Number of Rows in Target Range.
        NumberOfRows = rng.Rows.Count
Debug.Print "Number of Rows = " & NumberOfRows
    Else
        ' Inform user.
        MsgBox "No empty cells" ' Highly unlikely.
    End If

End Sub

Count cells that contains formula

1 Answers1

xlValues of LookIn Saves the Day