Making a CountRows function in Excel

Question

I am trying to make a simple countRows function that will count the number of cells I have in a dynamic range. Basically if I have values in cells, say B2:B500, the count would return 499. However next time around values are in cell B2:B501, the count would return 500. But you wouldn't have to do anything to the cell in which you typed in the formula.

I thought if I reference the cell as a Variant, then any value could be accepted. Then find the Address of that cell and return the Count of a Range. But I get a #Value error.

Public Function countRows(startRange As Variant)

    Dim rng As Range
    Set rng = startRange.Address


    If IsEmpty(Range(rng, rng.End(xlDown))) = True Then
        countRows = 1
    Else
        countRows = Range(rng, rng.End(xlDown)).Rows.Count
    End If

End Function

Range.Address returns a string. Set rng = startRange.Address Will throw an error use Set rng = startRange — , Jul 29 '17 at 17:14
If you are going to assume that startRange is a range than you should type cast it as a range and not a variant. The rng varible does nothing for your code. You should eliminate it. — , Jul 29 '17 at 17:19
@YowE3K I believe OP is trying to create something from scratch. Just saying. — Tehscript, Jul 29 '17 at 18:53
@Tehscript There have been so many questions where the poster ends up saying "Ohh - I never knew there was a function to do that!" that I thought I should mention it just in case. (In this question, the "But you wouldn't have to do anything to the cell in which you typed in the formula." made me think that they didn't know about `COUNTA`.) — YowE3K, Jul 29 '17 at 19:04
@YowE3K You are absolutely right. I offered him a vba solution and explained upon his request but he didn't respond afterwards which made me think that he needs a solution from the scratch, deleted my comments and shared this with you. Anyways, it is kinda annoying when people ask questions and disappear, or don't care to drop a comment when they have what they need. — Tehscript, Jul 29 '17 at 20:09
@JackArmstrong, you do not need to use `If IsEmpty(Range(rng, rng.End(xlDown))) = True Then` it is the same as `If True = True Then` (for empty cells anyway). ... just use `If IsEmpty(Range(rng, rng.End(xlDown))) Then` — jsotola, Jul 30 '17 at 00:44
@JackArmstrong, please clarify ... are you counting all cells in a dynamic range? are you skipping blank cells? what determines the extent of the dynamic range? — jsotola, Jul 30 '17 at 00:48
@JackArmstrong I rolled-back the question to a point where the code was not working. A question asking why isn't this code working that shows the code that does work is pointless to anyone having a similar problem. — YowE3K, Jul 30 '17 at 21:09
@JackArmstrong Do you realise that if you use your function by (for instance) inserting a formula of `=countRows(B2)` in cell C5, and it returns 499 because cells B2:B500 are used, and then you insert some extra rows (perhaps row 501 and 502), cell C5 will remain as 499 because the function is only dependent on cell B2? You should really pass B2:B500 (or B:B) to your function so that Excel knows it needs to recalculate the function if anything within that range changes. (Or you could mark the function as `Volatile`, but that comes with a performance hit and is not recommended.) — YowE3K, Jul 30 '17 at 21:15
DO NOT use an naked `Range()` because it assumes `ActiveSheet.Range()` and that might not be your intent. — John Alexiou, Jul 30 '17 at 22:16

John Alexiou · Accepted Answer · 2017-08-01T11:47:58.337

1

This is the code I have used for many years successfully under many different worksheets. It handles many cells, singular cells or empty cells.

Public Function CountRows(ByRef r As Range) As Long
    If IsEmpty(r) Then
        CountRows = 0
    ElseIf IsEmpty(r.Offset(1, 0)) Then
        CountRows = 1
    Else
        CountRows = r.Worksheet.Range(r, r.End(xlDown)).Rows.count
    End If
End Function

Public Function CountCols(ByRef r As Range) As Long
    If IsEmpty(r) Then
        CountCols = 0
    ElseIf IsEmpty(r.Offset(0, 1)) Then
        CountCols = 1
    Else
        CountCols = r.Worksheet.Range(r, r.End(xlToRight)).Columns.count
    End If
End Function

edited Aug 01 '17 at 11:47

answered Jul 30 '17 at 22:15

John Alexiou

28,472
11
77
133

EDIT: Function return should have been a 32-bit integer (`Long` type) – John Alexiou Aug 01 '17 at 11:48
NOTE: A lot of other implementations contain a naked `Range()` object, which is equivalent to `ActiveSheet.Range()` and that might be undesirable behavior. The above code is designed to be worksheet agnostic. – John Alexiou Aug 02 '17 at 14:16

curious · Answer 2 · 2017-08-01T04:38:13.093

It's not entirely clear what you are looking for, when you mentioned there are values in cells "B2:B500" and the count should return 499, as there could be a few possible scenarios:

You simply want to count the rows in the range "B2:B500". The code will be:

Range("B2:B500").Rows.Count

You want to count the non-blank cells in the range "B2:B500". In that case, as suggested in the comments:

WorksheetFunction.CountA(Range("B2:B500"))

As indicated in your code rng.End(xlDown), you probably want to the count continuous non-blank cells starting with the range "B2" in the overall range "B2:B500". You may create a function like this:

Public Function countRows(rng As Range) As Long
    Dim rw As Range
    For Each rw In rng
        If IsEmpty(rw) Then Exit For
        countRows = countRows + 1
    Next
End Function

Clarification:

Based on subsequent comments, I thought it's worth explaining why the variable "countRows" wasn't initialized by adding a line countRows = 0.

Certain programming languages like assembly language, C, C++ require explicit initialization. This was intentionally so designed due to the philosophy in which conflicts between performance and safety were generally resolved in favor of performance.

However, such is not the case with other programming languages like VBA or Java.

Speaking about VBA, during macro run, all the variables are initialized to a value. A numeric variable is initialized to zero, a variable length string is initialized to a zero-length string (""), and a fixed length string is filled with the ASCII code 0. Variant variables are initialized to Empty. An Empty variable is represented by a zero in a numeric context and a zero-length string ("") in a string context.

Therefore a separate line of code countRows = 0 wasn't added in the above code block.

While coding, one need to keep this in perspective as the same might not be true for other languages.

hi ja72 you likely didn't test the code n hence assuming "Very very slow code", even if all rows have data (1048576 rows max possible in the current excel version), it barely take a second to count that. on your 2nd pt, pl note the line`Public Function countRows(rng As Range) As Long`. VBA default value for Long is 0 and so `countRows = 0` isn't required — curious, Jul 31 '17 at 00:50
For code that might have to call `CountRows()` multiple times the delay adds up. I don't see why not use `End(xlDown)`. I think it is in bad taste to rely on the compiler default values. Even with `C#` which requires default values of zero, the compiler enforces setting a value before usage. — John Alexiou, Jul 31 '17 at 01:08
i actually started with End(xlDown) but it was turning out to be unnecessarily long n hence opted for the short n sweet For Each loop as there's no noticeable difference in performance. btw i took a look at your post wch is good, just a few improvements require to make it bug-free on extreme cases. reg your concern on initialization, i have added my explanation in the answer — curious, Aug 01 '17 at 04:43

Making a CountRows function in Excel

2 Answers2

Linked