If you are busy writing a macro for Excel and want to figure out the number of rows currently being used for some sort of calculation that you want to run against the data, then the easiest way of achieving this is by making use of the ActiveSheet.UsedRange object.
The following code will pop up a message box dialog displaying the number of used rows on your Excel sheet:
MsgBox ActiveSheet.UsedRange.Rows.Count
(Note that empty rows are considered ‘used’ if data follows the empty row).