I'm using a .NET library, Excel-DNA, that exposes Excel's C-API to C# code. Using the xlfGetDocument function like this (I specifically want ints):
int rowLast = ((int)(double)XlCall.Excel(XlCall.xlfGetDocument, 10, sheetName) > 0) ? (int)(double)XlCall.Excel(XlCall.xlfGetDocument, 10, sheetName) - 1 : 0;
int colLast = ((int)(double)XlCall.Excel(XlCall.xlfGetDocument, 12, sheetName) > 0) ? (int)(double)XlCall.Excel(XlCall.xlfGetDocument, 12, sheetName) - 1 : 0;
returns me a rowLast and a rowFirst that is somewhat beyond my data range. According to the Steve Dalton book "Financial Applications using Excel add-in development in C/C++", the '10' and '12' arguments to this function should return the last USED col/row. But this is not the case.
Why is this?
Following from getting the used-range of a sheet above, I'm using Excel-DNA to get the actual contents:
contentReference = new ExcelReference(0, rowLast, 0, colLast);
contents = (object[,])contentReference.GetValue();
How can I get the actual used range without applying a filter to contents (seems like that should just be unnecessary overhead)?
Because Excel uses a sparse matrix storage scheme it considers the last used row/column to be the row and column of the last cell that contains data or is formatted (or did contain data or formatting).
You are probably better off using the COM interface to do a Range.Find * if you want the real last used range.