Tricky question about rows of cells.

  • Thread starter Thread starter Robert Crandal
  • Start date Start date
R

Robert Crandal

At the start of the day, our company has 0 transactions. Whenever
a transaction occurs, we will save that transaction data in a single
row of cells.

Therefore, once 5 transactions have been completed, my spreadsheet
will consists of 5 rows of distinct data.

At the end of any given day, we will have completed N transactions
which means that the spreadsheet will contain N rows of distinct
transaction data. My question is: What is a good way to calculate
or determine HOW MANY rows of data have been filled with
transaction data?? (In other words, I'm trying to calculate "N")

Got any ideas?

Thank you!
 
Cells.SpecialCells(xlCellTypeLastCell).Row

Will give you the row number of the last used cell.

Sam
 
Can this formula be plugged into any of my cells?? Or MUST I
use this code only in a Visual Basic module??
 
Hi,

This returns the last used row of column A so if you start in Row 1 it will
equal the amount of filled rows.

=SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A1000<>"")))

If you start in another row (say) 5 then simply subtract 4

=SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A1000<>"")))-4

Mike


"Robert Crandal
 
Back
Top