Determine the number of rows in a range

Question

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

User · Answer

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.

User · Answer

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

User · Answer

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    amp  strArea  amp      Range  cc rev count   Formula   cc rev rows

User · Answer

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

User · Answer

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

User · Answer

Sheet1 Range  myrange   Rows Count

[vba] Determine the number of rows in a range

Examples related to vba

Examples related to excel