Tricky question about rows of cells.

[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!

 

[Sam Wilson]

Cells.SpecialCells(xlCellTypeLastCell).Row

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

Sam

 

[Robert Crandal]

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

 

[Peter T]

Look at the COUNTA() worksheet function.

Regards,
Peter T

 

[Mike H]

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