Get the last cell (column, row) of a Excel range object

Question

I have a Microsoft.Office.Interop.Excel.Range object and want to estimate the end-coordinates of that range ie (last column, last row).

There are no properties like LastCol, LastRow. The only properties are Column and Row, which specify the first cell. But how can I get the last cell of the range?

Answer 1

there is two way to do this :

1. Using Worksheet.UsedRange to determine the range. This will give you a range like A1:F10, or use Worksheet.UsedRange.SpecialCells(IExcel.XlCellType.xlCellTypeLastCell) to obtain F10.

2. With Range.End[IExcel.XlDirection] property, it returns the last continuous no-empty cell in the specified direction. Note that if the Range is empty, it will return the first no-empty cell or last cell(when excel reach its boundaries) in the given direction. Ex: the whole column A is empty, Range["A1"].End[IExcel.XlDirection.xlDown] will be A65535(last row in excel 97-2003, A1048576 for excel 2007)

//Just In case if you are wondering what IExcel is
using IExcel = Microsoft.Office.Interop.Excel;

Answer 2

This should get the first first and last cell of the range:

1. Initiate Excel.

2. Open workbook.

3. Select your range, in this case I got the used range of the active sheet.

4. Call the get_address method of the range.

5. Split the result of get_address on the colon.

6. The first value of the array resulting from the split will have the beginning cell.

7. The second value of the array resulting from the split will have the ending cell.

8. Replace the dollar signs with nothing and you will have the beginning and ending cells for the range.

   Microsoft.Office.Interop.Excel.Application excel = new Application();
   Microsoft.Office.Interop.Excel.Workbook workBook =
       excel.Workbooks.Open(fileLocation);
   Microsoft.Office.Interop.Excel.Worksheet sheet = workBook.ActiveSheet;
   Microsoft.Office.Interop.Excel.Range range = sheet.UsedRange;
   string address = range.get_Address();
   string[] cells = address.Split(new char[] {':'});
   string beginCell = cells[0].Replace("$", "");
   string endCell = cells[1].Replace("$", "");
   workBook.Close(true);
   excel.Quit();
   

If you want the last column and last row relative to the beginning of the range you can do the following:

    int lastColumn = range.Columns.Count;
    int lastRow = range.Rows.Count;

Answer 3

Using Excel 2013, we've had lots of cases where UsedRange and xlCellTypeLastCell simply gave horribly wrong values.

For example, we'd have a worksheet containing just 7 columns, and sometimes these two functions would tell our C# code that there were 16,000+ columns of data.

The only method I found which truly worked out the bottom-right cell containing data was to use the tips in this suggestion:

sheet.Cells.Find()