Locate last populated row/column in Excel with Win32::OLE

[mrichardson]

Hello,

I'm working on a Perl script that needs to locate the last populated
row/column in an Excel sheet. I have tried the following:

my $TransCharWSLastRow = $TransCharWS->UsedRange->Find({What=>"*",
SearchDirection=>xlPrevious, SearchOrder=>xlByRows})->{Row};
my $TransCharWSLastCol = $TransCharWS->UsedRange->Find({What=>"*",
SearchDirection=>xlPrevious, SearchOrder=>xlByColumns})->{Column};

However, both lines return "902" which doesn't seem especially useful.
I'd be willing to wade through some documentation if anyone can point
me in the right direction.

Thanks,
Matt

 

[Brian Helterline]

Hello,

I'm working on a Perl script that needs to locate the last populated
row/column in an Excel sheet. I have tried the following:

my $TransCharWSLastRow = $TransCharWS->UsedRange->Find({What=>"*",
SearchDirection=>xlPrevious, SearchOrder=>xlByRows})->{Row};
my $TransCharWSLastCol = $TransCharWS->UsedRange->Find({What=>"*",
SearchDirection=>xlPrevious, SearchOrder=>xlByColumns})->{Column};

It seems to work for me.

Other Ideas:

If you know that your UsedRange is completely populated, then you can
count the number of rows and columns to figure out the last cell.

# untested
my $used = $TransCharWS->UsedRange;
my $rows = $used->Rows->Count;
my $cols = $used->Columns->Count;
my $last = $used->Cells( $rows, $cols );

If this isn't a safe assumption (I would guess it is not), but you know
all the data is continuous, then you can select the beginning of the
UsedRange and scroll down and over to the last populated cell (like hitting
END+DOWN-ARROW, etc.

#again, untested
my $used = $TransCharWS->UsedRange->Cells( 1, 1 ); # first cell is active
$used = $used->End(xlDown); # last row
$used = $used->End(xlToRight); # last column of last row

However, both lines return "902" which doesn't seem especially useful.

Can't reproduce this