how do i determine the number of rows in a range in excel using VBA

Question

in my code i know the range name of the start of a list - 1 column wide 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

Answer 1

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

Answer 2

I'm 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.

Answer 3

that single last line worked perfectly GSerg thanks 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
    

Answer 4

You can also use: Range( RangeName ).end(xlDown).row to find the last row with data in it starting at your named range.

Answer 5

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