Simon Lloyd
05-21-2007, 03:21 AM
Hi all, i have answered a couple of posts lately where the Op's have asked for a way to count used rows with a worksheet function - well i supplied the answer like this:
for counting rows, you need to create a dynamic Named Range for column A using, INSERT, NAMES, DEFINE - type a name for your range (i used ColA) and then copy and paste this in to the Refers To window =OFFSET(Sheet1!$A$1,0,0, COUNTA(Sheet1!$A:$A),1) now click ADD (a great worked example and explanation can be found here (http://www.contextures.com/xlNames01.html), once you have created your name range enter this in to any cell =ROWS(ColA) where ColA was my named range.
it works well and if you want to count from row 2 then =ROWS(ColA)-1 as =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1) still counts total number of rows. The anomalie i found was if i changed the OFFSET to =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A2:$A65536),1) then using =ROWS(ColA) showed a different result depending on where you used it..if you used it in row 1 then you get the total count of used rows from row 2 all well and good but try to copy or type =Rows(ColA) elsewhere!, you get an unexpected result for some reason every time you use the formula after the initial one the Dynamic Named Range Refers To box has a different formula in it?
Any ideas or suggestions?
EDIT: Yes i did forget to say that COUNTA(A:A) does the same job, completly slipped my mind at the time, sledgehammer to crack a nut eh?
for counting rows, you need to create a dynamic Named Range for column A using, INSERT, NAMES, DEFINE - type a name for your range (i used ColA) and then copy and paste this in to the Refers To window =OFFSET(Sheet1!$A$1,0,0, COUNTA(Sheet1!$A:$A),1) now click ADD (a great worked example and explanation can be found here (http://www.contextures.com/xlNames01.html), once you have created your name range enter this in to any cell =ROWS(ColA) where ColA was my named range.
it works well and if you want to count from row 2 then =ROWS(ColA)-1 as =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A),1) still counts total number of rows. The anomalie i found was if i changed the OFFSET to =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A2:$A65536),1) then using =ROWS(ColA) showed a different result depending on where you used it..if you used it in row 1 then you get the total count of used rows from row 2 all well and good but try to copy or type =Rows(ColA) elsewhere!, you get an unexpected result for some reason every time you use the formula after the initial one the Dynamic Named Range Refers To box has a different formula in it?
Any ideas or suggestions?
EDIT: Yes i did forget to say that COUNTA(A:A) does the same job, completly slipped my mind at the time, sledgehammer to crack a nut eh?