C# – VSTO – Get Last Used Row or Cell in Excel Spreadsheet

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:


Cells.SpecialCells(xlCellTypeLastCell).Row

Hope that helps!

Cheers,
Byron

, , ,

2 Responses to C# – VSTO – Get Last Used Row or Cell in Excel Spreadsheet

  1. Jonatan December 19, 2011 at 7:27 pm #

    Thank you, Byron. This is helpful. Which assemblies need to be referenced? For me, Microsoft.Office.Interop.Excel does not do the trick yet.

  2. Jonatan December 19, 2011 at 8:01 pm #

    Strangely, the SpecialCells property works for Range as opposed to Cells:

    private int lastRowForCol(Microsoft.Office.Interop.Excel.Worksheet sh, int col)
    {
    string first = Convert.ToChar(col + 64) + “1”;
    string last = Convert.ToChar(col + 64) + “10000”;

    Microsoft.Office.Interop.Excel.Range rn = sh.Range[first,last] ;

    int r = rn.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
    return r;
    }

Powered by WordPress. Designed by Woo Themes