Display count of rows

[Guest]

I have been asked to display a count of the rows in each sheet of an Excel
file at the top of each sheet (so that the user does not have to scroll down
to see how many rows of data there are). I want to do this through code.

Is this the best way to get a count of the rows:
ActiveSheet.UsedRange.Rows.Count
And I probably need to subtract 1 for the row with headings.

I also need to know--is there a way to display the count of rows above row 1
(where the column headings are)? Is there a row zero?

Thank you for any help you can give me,
Judy

 

[JE McGimpsey]

One way:

=COUNTA(A:A)-1

There is no row zero. You could insert a row above the headings (which
would then be in Row 2) and use

=COUNTA(A:A)-2

 

[Biff]

Hi!

Another way that accounts for empty rows within the range. Returns the row
number of the last cell in column A that contains data.

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=MAX(IF(A2:A65536<>"",ROW(2:65536)))

Assumes row 1 (A1) is a header.

Note also, will not "count" cells that contain formula blanks. Suppose that
cell A100 has a formula returned value of "X" and there are formula blanks
from A101 to A200. The above formula will return 100 as the last row in
column A that contains data.

Biff