Finding total # of records in an Excel file without scrolling

  • Thread starter Thread starter LQ
  • Start date Start date
L

LQ

I am new to Excel 2003 and would like to know how I can find the total number
of records / rows in a particular file without scrolling down until I see the
last row entered. Is there someplace that gives me a total?
 
Hi,

One way,

Alt +F11 to open vb editor. Right click 'This workbook' and insert module
and paste this in on the right.

Call with
=lastrow()
in a worksheet cell


Function lastrow()
lastrow = ActiveSheet.UsedRange.Rows.Count
End Function

Mike
 
In an empty cell, enter =COUNTA(A:A) where A is the column where you want to
count non-empty cells. Note that some columns may have more entries than
others, so it matters on which column you run this function. Also, note that
this counts the number of non-blank cells in the column; it does not return
the number of the last non-blank row if there are blank cells within the
column. Example: Say a1:a10 contains names, b1:b10 values, non of which are
blank, b11 is blank, b12 contains =SUM(B1:B10)
CountA(A:A) returns 10, CountA(B:B) returns 11, NOT! 12.
Another caveat: visially, a zero-length character string looks just like a
blank cell, but it *IS* counted by COUNTA.
 
Instead of scrolling, just type CTRL-down arrow.

tedmi said:
In an empty cell, enter =COUNTA(A:A) where A is the column where you want to
count non-empty cells. Note that some columns may have more entries than
others, so it matters on which column you run this function. Also, note that
this counts the number of non-blank cells in the column; it does not return
the number of the last non-blank row if there are blank cells within the
column. Example: Say a1:a10 contains names, b1:b10 values, non of which are
blank, b11 is blank, b12 contains =SUM(B1:B10)
CountA(A:A) returns 10, CountA(B:B) returns 11, NOT! 12.
Another caveat: visially, a zero-length character string looks just like a
blank cell, but it *IS* counted by COUNTA.
Click to expand...
 
CTRL-End will take you to the cell that Excel thinks is the last used
cell (though this might be beyond the last record). CTRL-Home will
take you back to the first cell.

Hope this helps.

Pete
 
Back
Top