Get range address in Excel using vb.net

Question

I am trying to dynamically get the range of cells in Excel using VS 2005 vb.net. This works oRange = oSheet.Range(oSheet.Cells("A1"), ("U281")).Select, but "U281" will not always be the last cell in the range. So how would I dynamically get the last cell with data in it with the same format as U281.

Thanks,

Lora

Answer 1

To get the last cell, use this formula:

lastCell = oSheet.Cells.SpecialCells(XlCellType.xlCellTypeLastCell)

This works fine, unless you have some users who have been to some strange areas of the spreadsheet and done things that they have since deleted. The above formula will return the last cell as being wherever they went on their strange journeys, even if its now all blank.

In my case, I have to process every cell in a lot of spreadsheets where the user went down to row 60,741 and column 50 even though there is only a couple of hundred lines of actual data. To avoid this massive blank region, you want to search for the maximum row and the maximum column that have data and use that cell as the bottom right corner of your square of cells that has data, as below (it's C# but shouldn't be hard to translate):

Microsoft.Office.Interop.Excel.Range maxCell = 
    (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[worksheet.Cells.Find("*",
    (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1],
    Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues,
    Microsoft.Office.Interop.Excel.XlLookAt.xlWhole,
    Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows,
    Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious, 
    false, false, missing).Row,
    worksheet.Cells.Find("*", 
    (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1], 
    Microsoft.Office.Interop.Excel.XlFindLookIn.xlValues,
    Microsoft.Office.Interop.Excel.XlLookAt.xlWhole, 
    Microsoft.Office.Interop.Excel.XlSearchOrder.xlByColumns,
    Microsoft.Office.Interop.Excel.XlSearchDirection.xlPrevious, 
    false, false, missing).Column];

This can save you a lot of processing time.