Oddity on getting last row for Excel

Add-in Express™ Support Service
That's what is more important than anything else

Oddity on getting last row for Excel
 
George Spears




Posts: 79
 Joined: 2010-05-06
Hello,

I am writing an Excel plug in. That plugin loops through all rows on a spreadsheet and then does some specific processing. This code used to work fine, but now, for some reason, it returns that there are 1048415 rows in one specific worksheet. Any idea as to WHY? My guess is something got saved to the spreadsheet. Is there some specific meaning to this number? (1048415)?

Here is the code I am running to get my last row... 


procedure TAcctDetailsForm.GetSheetBoundaries;
var
  IWks: _Worksheet;

begin
  // Get the Last Row and Column for the ACTIVE Worksheet
  // Example at http://www.tek-tips.com/viewthread.cfm?qid=1670702
  IWks := nil;

  BoundRec.FirstCol := 1;
  BoundRec.FirstRow := 1;

  try
    // Get the Handle to the Active Worksheet...
    aws.QueryInterface(IID__Worksheet, IWks);

    try
{$IFDEF Excel2000}
      BoundRec.LastRow := XLSheet.Cells.Find('*', EmptyParam, EmptyParam, EmptyParam, xlByRows, xlPrevious, EmptyParam, EmptyParam).row;
      BoundRec.LastCol := XLSheet.Cells.Find('*', EmptyParam, EmptyParam, EmptyParam, xlByColumns, xlPrevious, EmptyParam,
        EmptyParam).Column;
{$ELSE}
      BoundRec.LastRow := IWks.UsedRange[LOCALE_USER_DEFAULT].Rows.Count;
      BoundRec.LastCol := IWks.UsedRange[LOCALE_USER_DEFAULT].Columns.Count;

{$ENDIF}
    except
      on E: Exception do
      begin

        BoundRec.LastRow := 0;
        BoundRec.LastCol := 0;
      end;
    end;

  finally
    IWks := nil;
  end;
end;


Thanks
Posted 11 Dec, 2014 07:24:44 Top
Dmitry Kostochko


Add-in Express team


Posts: 2875
 Joined: 2004-04-05
Hi George,

This code used to work fine, but now, for some reason, it returns that there are 1048415 rows in one specific worksheet. Any idea as to WHY?


I think this specific worksheet really contains so many rows. The point is that cells containing values and cells with a changed format are included in the UsedRange property.

You can try to delete row 1048415 in this worksheet and re-test your code.
Posted 12 Dec, 2014 03:44:27 Top