How to Get The Number of Rows in an Excel Spreadsheet with Applescript

I do a lot of excel applescripting, and before learning how to use “Used Range” I would make a guestimate of how many rows I would probably need to to transfer data from one sheet to another. In the case below I would assume that none of my documents had more than 2,000 rows to copy.

Old Way:

set var1 to value of range("a1:a2000")

With the Used Range you can select the exact amount of data you need with no guess work, thus eliminating any errors or missing information resulting in not grabbing enough lines from your source document.

Ex:

set theRowCount to getRowCount()

tell application "Microsoft Excel"

set var1 to value of range("a1:a"& theRowCount)

end tell

To use the getRowCount() function you must paste the below text into the top of your Applescript doc.

to getRowCount()

tell application "Microsoft Excel"
 tell active sheet
 tell used range
 set rc to count of rows
 end tell
 return rc
 end tell

end tell

end getRowCount

Next, when you would like to get the row count of the active document in excel, use the getRowCount() function in your code.

getRowCount()

 

NOTE: This will not work if it is already inside of an Excel Tell Statement.

Will work:

tell application "Microsoft Excel"
 --some code...
 end tell

getRowCount()

tell application "Microsoft Excel"
 --some code...
 end tell

 

Will Not Work:

tell application "Microsoft Excel"

getRowCount()

end tell