Dynamic Range Issue

[Nlreid]

Hi,

I have a page at the beginning of my workbook that provides summary data on the rest of the 11 worksheets. For each of the 11 worksheets, I'd want to put the number of rows in the worksheet (# of rows minus 1 for the header would be even better). I'm running a macro that automatically updates the rows though, so I must use a dynamic range.

The 1st worksheet's name is "DF_PTYPESTYPE" and in the cell that I would like the row count to be placed I have the following formula:

=OFFSET(DF_PTYPESTYPE!$A$1,0,0,COUNTA($A:$A),1)

However, it is returning simply the data that is in cell A1 of the worksheet, rather than the count of rows in the worksheet.

How can I fix this formula?

Thanks so much!

 

[T. Valko]

Hi,

I have a page at the beginning of my workbook that provides summary data on the rest of the 11 worksheets. For each of the 11 worksheets, I'd want to put the number of rows in the worksheet (# of rows minus 1 for the header would be even better). I'm running a macro that automatically updates the rows though, so I must use a dynamic range.

The 1st worksheet's name is "DF_PTYPESTYPE" and in the cell that I would like the row count to be placed I have the following formula:

=OFFSET(DF_PTYPESTYPE!$A$1,0,0,COUNTA($A:$A),1)

However, it is returning simply the data that is in cell A1 of the worksheet, rather than the count of rows in the worksheet.

How can I fix this formula?

Thanks so much!

Maybe this:

=COUNTA(DF_PTYPESTYPE!$A$2:$A$65536)

 

[Aladin Akyurek]

Hi,

I have a page at the beginning of my workbook that provides summary data on the rest of the 11 worksheets. For each of the 11 worksheets, I'd want to put the number of rows in the worksheet (# of rows minus 1 for the header would be even better). I'm running a macro that automatically updates the rows though, so I must use a dynamic range.

The 1st worksheet's name is "DF_PTYPESTYPE" and in the cell that I would like the row count to be placed I have the following formula:

=OFFSET(DF_PTYPESTYPE!$A$1,0,0,COUNTA($A:$A),1)

However, it is returning simply the data that is in cell A1 of the worksheet, rather than the count of rows in the worksheet.

How can I fix this formula?

Thanks so much!

If column A on DF_PTYPESTYPE is numeric, the following yields the last record:

=MATCH(9.99999999999999E+307,DF_PTYPESTYPE!$A:$A)

If you want the number of cells between DF_PTYPESTYPE!$A$1 and the cell of the last record...

=MATCH(9.99999999999999E+307,DF_PTYPESTYPE!$A:$A)-MIN(ROW(DF_PTYPESTYPE!$A$1))+1

If column A is text, replace the big number with:

REPT("z",255)

in the foregoing formulas.