Is there a better way?

[St!ff M!ttens]

I've gotten this far:

tell application "Microsoft Excel"
try
set theProps to (properties of used range of sheet 1 of active
workbook)
set theArea to (areas of theProps)
set theRange to (item 1 of theArea)
set theNextRow to ((first row index of (get end theRange direction
toward the bottom)) + 1)
on error errMsg number errNum
display dialog "Error: " & errNum & return & errMsg
end try
end tell

It works, but I have to think that there is a more concise way to get
the next row down from the last row with data in it. Anybody got a
suggestion?

 

[St!ff M!ttens]

Nevermind, I just figured out a way:

set theNextRow to ((first row index of (get end (get item 1 of (get
areas of (get properties of used range of sheet 1 of active workbook)))
direction toward the bottom)) + 1)

Previously I had tried to combine the individual statements into one
nested statement and it didn't work, because I hadn't explicitly used
the get command. I incorrectly assumed that:

properties of used range of sheet 1 of active workbook

implied:

get properties of used range of sheet 1 of active workbook

Still, if anyone has a better way, I'd still like to hear about it.

 

[Paul Berkowitz]

Nevermind, I just figured out a way:

set theNextRow to ((first row index of (get end (get item 1 of (get
areas of (get properties of used range of sheet 1 of active workbook)))
direction toward the bottom)) + 1)

Previously I had tried to combine the individual statements into one
nested statement and it didn't work, because I hadn't explicitly used
the get command. I incorrectly assumed that:

properties of used range of sheet 1 of active workbook

implied:

get properties of used range of sheet 1 of active workbook

Still, if anyone has a better way, I'd still like to hear about it.

You have the right idea, but it can be a lot more concise. You don't need to
bother with areas or properties.

tell application "Microsoft Excel"
set theNextRow to (first row index of (get end (used range of active
sheet) direction toward the bottom)) + 1
end tell




--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[Paul Berkowitz]

You have the right idea, but it can be a lot more concise. You don't need to
bother with areas or properties.

tell application "Microsoft Excel"
set theNextRow to (first row index of (get end (used range of active
sheet) direction toward the bottom)) + 1
end tell

And here's an alternative way - no better, but just as good:

tell application "Microsoft Excel"
set theNextRow to (first row index of (special cells (used range of
active sheet) type cell type last cell)) + 1
end tell

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.