I have had the opportunity to get my fingers dirty with Visual Studio Tools for Office, at long last. My project brought me back to an old familiar problem in Excel, finding the last used row in a spreadsheet.
This applies equally to C#, VB, and VBA. You just have to get access to the Worksheet object of the sheet you’re working on.
Here’s how in C# (I’m doing an Excel Workbook project):
int lastRow = Globals.Sheet1.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
The magic is using the SpecialCells property of the Cells object. If you’re doing this in straight up VBA, or you have all of the proper assemblies referenced, you could probably shorten it to just:
Hope that helps!