Geek Answers Handbook

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

I have a Microsoft.Office.Interop.Excel.Range object and you want to evaluate the final coordinates of this range, i.e. (last column, last row).

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

+7
c # excel vsto
source share
3 answers

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

  • Initialize Excel.

  • Open the book.

  • Select a range, in this case I got the used range of the active sheet.

  • Call the get_address range method.

  • Divide the result of get_address with a colon.

  • The first value of the array resulting from the division will have an initial cell.

  • The second value of the array obtained by splitting will have a final cell.

  • Replace the dollar signs with nothing and you will have the start and end 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 to refer to the beginning of the range, you can do the following:

  int lastColumn = range.Columns.Count; int lastRow = range.Rows.Count;
+6
source share

There are two ways to do this:

  • Using Worksheet.UsedRange to determine the range. This will give you a range such as A1: F10, or use Worksheet.UsedRange.SpecialCells(IExcel.XlCellType.xlCellTypeLastCell) to get F10.

  • With the Range.End[IExcel.XlDirection] property, it returns the last continuous non-empty cell in the specified direction. Note that if Range is empty, it will return the first non-empty cell or the last cell (when excel reaches its borders) in that direction. Example: entire 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;

+8
source share

Using Excel 2013, we had many cases where UsedRange and xlCellTypeLastCell just gave terribly wrong values.

For example, we will have a worksheet containing only 7 columns, and sometimes these two functions will tell our C # code that there are 16,000 + data columns.

The only method I found that really designed the bottom right cell containing the data was to use the hints in this sentence:

sheet.Cells.Find ()

0
source share

More articles:


All Articles