How to get Last Active Cell in Excel 2007

Question

I am working with C++ unmanaged and Excel 2007. I am using a call to the Excel4 API to get the range of cells selected by the user.

When the user selects what I call a "common" range, this call returns a range like this "R1C1:R4C3", which is exactly the format that I need for doing other operations in my application.

However, when the user decides to select whole columns then this API call returns a range only with columns, something like this "C1:C3", which of course is a valid excel range, but it means to me that I have to modify it so it looks like the the range above; in other words I have to manually add it the rows range.

So I take this range "C1C3" and change it to be "R1C1:R65534C3".

Perhaps at this point you already noticed my problem, as you can see I'm hard coding the rows range to be the max numbers of rows supported by Excel 2003 (the application also works with this version of excel).

This of course is not what I'm looking for, because once I get the selected range, I read the info in that range and use that info to make a query to the server, so imagine that the user only entered data until row 534 and still my query will have 65000 rows with nothing.

So my question is how can I get the last cell in which the user entered data, I think it is called the last active cell, so I can delimit my range until that row and please remember that I'm working with unmanaged code.

Answer 1

Try casting range.selection to a Range object.

Answer 2

The Excel 4 API? Really?

There's a command xlcSelectEnd which you can use to jump to the last cell with text entered in it in any direction from a given cell.

Answer 3

region Get Last Used Row

  Excel.ApplicationClass app = new Excel.ApplicationClass();
        Excel.Workbook workBook = app.Workbooks.Open(_PathFile,
                 System.Reflection.Missing.Value,
                 System.Reflection.Missing.Value,
                 System.Reflection.Missing.Value,
                 System.Reflection.Missing.Value,
                 System.Reflection.Missing.Value,
                 System.Reflection.Missing.Value,
                 System.Reflection.Missing.Value,
                 System.Reflection.Missing.Value,
                 System.Reflection.Missing.Value,
                 System.Reflection.Missing.Value,
                 System.Reflection.Missing.Value,
                 System.Reflection.Missing.Value,
                 System.Reflection.Missing.Value,
                 System.Reflection.Missing.Value);
        // Get the active worksheet using sheet name or active sheet
        Excel.Worksheet workSheet = (Excel.Worksheet)workBook.ActiveSheet;
        // Find the last real row
        int _InLastRow = workSheet.Cells.Find("*",System.Reflection.Missing.Value,
            System.Reflection.Missing.Value, System.Reflection.Missing.Value, Excel.XlSearchOrder.xlByRows,Excel.XlSearchDirection.xlPrevious, false,System.Reflection.Missing.Value,System.Reflection.Missing.Value).Row;
#endregion