Get UsedRange for one column only in Excel Interop

Question

Using Microsoft's Excel Interop I am reading a spreadsheet. I have no problems getting the used range for the entire sheet, but I want to get the number of rows used for one column only. Here is an example of my sheet:

As you can see, the used range for Column A is only 3 rows, whilst for Column B it is 5 rows. When I used my current code:

int rows = sheet.UsedRange.Rows.Count;

..it always returns "5". How do I specify that I only want the used range for one cell?

Answer 1

The UsedRange in Excel applies to the worksheet not individual columns. See here. You can get the last row for a particular column using something like this:

With yourExcelSheet
    lrow = .Cells(.Rows.Count, lrCol).End(Excel.XlDirection.xlUp).Row
End With

Where 'yourExcelSheet' is the worksheet and needs to be declared/assigned, 'lrow' is the last row you require and 'lrcol' is the column number (A=1, B=2 etc.)

Apols but this snippet is derived from vb.net/vba and not c# so you will need to deal with any required conversion; but thought it may help.

Answer 2

To get the Used Cells in Column A you can try this way

// Get the last cell used in the Column A
 var lastCell = sheet.Range["A:A"].SpecialCells(
    Microsoft.Office.Interop.Excel.XlCellType.xlCellTypeLastCell
)
    // get the lastColumn used
    int lastcolumn = lastCell.column;

    // get the lastRow used
    int lastRow = lastCell.Row;