Applescript, Excel 2011 with "Used Range"

Question

Level 1

10 points

Applescript, Excel 2011 with "Used Range"

I have an excel spreadsheet that I have visible data in cells A1:H53. When I use applescript to obtain the last row, using the "used Range", I get a result of 534, not 53. I tried "clear all" on rows 54 - 534, but that does not solve the problem. Any ideas, why excel thinks 534 rows are used when I only have data in the first 53 rows?

The applescript is:

Tell Worksheet 1

set DestLastRowNum to count of rows of used range

End Tell

If I select the visible data (A1:H53), copy, and paste it into a new empty sheet, then the applescript returns the correct value of 53.

Any ideas why the original sheet gives the wrong information?

MacBook Pro (13-inch Mid 2012), OS X Yosemite (10.10.2), Microsoft Office 11

Posted on Mar 7, 2015 2:52 PM

Reply

Answer 1

Rob1sonjr Author

Level 1

10 points

Mar 8, 2015 9:23 AM in response to Rob1sonjr

Problem Solved:

I found a post on a microsoft forum about Excel. The Edit:Clear:ALL command does not change the previously used cells to unused, therefore the "Used Range" still includes the blank cells. You have to select the rows and Delete them, to get the "Used Range" reset.

Reply