Determine the number of rows in a range

I know the range name of the start of a list - 1 column wide and x rows deep.

How do I calculate x?

There is more data in the column than just this list. However, this list is contiguous - there is nothing in any of the cells above or below or either side beside it.

Answers


Function ListRowCount(ByVal FirstCellName as String) as Long
    With thisworkbook.Names(FirstCellName).RefersToRange
        If isempty(.Offset(1,0).value) Then 
            ListRowCount = 1
        Else
            ListRowCount = .End(xlDown).row - .row + 1
        End If
    End With
End Function

But if you are damn sure there's nothing around the list, then just thisworkbook.Names(FirstCellName).RefersToRange.CurrentRegion.rows.count


Sheet1.Range("myrange").Rows.Count

Why not use an Excel formula to determine the rows? For instance, if you are looking for how many cells contain data in Column A use this:

=COUNTIFS(A:A,"<>")

You can replace <> with any value to get how many rows have that value in it.

=COUNTIFS(A:A,"2008")

This can be used for finding filled cells in a row too.


You can also use:

Range( RangeName ).end(xlDown).row

to find the last row with data in it starting at your named range.


I am sure that you probably wanted the answer that @GSerg gave. There is also a worksheet function called rows that will give you the number of rows.

So, if you have a named data range called Data that has 7 rows, then =ROWS(Data) will show 7 in that cell.


That single last line worked perfectly @GSerg.

The other function was what I had been working on but I don't like having to resort to UDF's unless absolutely necessary.

I had been trying a combination of excel and vba and had got this to work - but its clunky compared with your answer.

strArea = Sheets("Oper St Report CC").Range("cc_rev").CurrentRegion.Address
cc_rev_rows = "=ROWS(" & strArea & ")"
Range("cc_rev_count").Formula = cc_rev_rows

Need Your Help

html select ondblclick mobile

javascript jquery html select

When I double click a option, it will execute javascript function.

Call controller method from JSP button in Spring MVC

java spring jsp spring-mvc

I would like to call a controller method using a button on a JSP page in Spring MVC, but I would like it to stay on a current page, don't reload it or anything, simply call a method. I found it dif...