Re: find last row in excel
Re: find last row in excel
- Subject: Re: find last row in excel
- From: Paul Berkowitz <email@hidden>
- Date: Sun, 31 Jul 2005 13:08:23 -0700
- Thread-topic: find last row in excel
Title: Re: find last row in excel
On 7/31/05 12:15 PM, "Robert Poland" <email@hidden> wrote:
>tell application "Microsoft Excel"
> set ur to UsedRange of ActiveSheet -- or Worksheet "Averages"
> set c to SpecialCells ur Type xlLastCell
> set x to Row of c
> set y to Value of Row x of ur
>end tell
>
>--
>Paul Berkowitz
> You were right I am using Excel X. Works great.
> The line "set c to SpecialCells ur Type
> xlLastCell" is a bit cryptic though.
What do you mean "cryptic"? If you make it more verbose by adding more words, it won't work since it will ruin the syntax. You'd like _me_ to add some more comments and explanation for you? Sure, glad to oblige.
The Table Suite in the Dictionary contains a SpecialCells command - you can find it there yourself (once you know about it! Of course it's not something you'd think to look for there, except that Excel often has proprietary commands where one would hope to find properties of objects. So you should always look tough the command [events] for things of interest.) Here's the entry:
SpecialCells: return a range referring to cells that match a certain criterion
SpecialCells Range -- the source range
Type xlNotes/xlConstants/xlFormulas/xlBlanks/xlAEReserved/xlLastCell/xlVisible -- the type of cells to return
[Value integer] -- what type of formulas and constants to return
Result: Range
It's a command that can find various different enumerated cells of special interest. xlLastCell is the one you want, since it returns the last cell of a range. Having chosen the UsedRange of a worksheet - which is an extremely useful property, something you're going to need in almost every Excel script you write - to specify just the range of the worksheet that has non-empty cells, the last cell* of that range will obviously be in the last row with content, so we'll be able to get the information we need from that. The syntax you need to use is illustrated by the dictionary. First you state the command itself SpecialCells followed by the Range you want it to act on. In this case that would be the variable ur I set to UsedRange. (Or you could dispense with variables but then you'd have to get the UsedRange again in the last line of the script which would be repeated work. That's why I use a variable ur. Then you must follow with the required Type parameter to say which variety of Special Cells you want. There is an enumerated choice of possibilities: the one you want is xlLastCell. (I suspect that the Value parameter might be able to return the row index, but I don't know which integer would be good for that.**) So that means the syntax for the whole command is
SpecialCells ur Type xlLastCell
or
SpecialCells (UsedRange of Worksheet "Averages") Type xlLastCell
if I weren't using the ur variable. The result of this command gets you the last cell, and by getting its 'Row' property – which gives the index of the first row of any range of cells within the worksheet as a whole: since the last cell is just a single cell its 'first row index' is in fact its row index within both the worksheet and the used range. So then we can use that index (x in my script) to get the value of Row x of the used range and thus the vlaue of the whole last row as a list.
*Now you might say "can I be sure that the last cell really is the one at the bottom"? I've never heard of anyone specifying a different direction, but it is possible. So in fact there is another command you might have used instead of SpecialCells, and you could be really sure. This is the LastCell command, also in the Table Suite, and you specify direction:
LastCell: Returns the cell at the end of the given range
LastCell Range -- the range of whicih to find the end
Direction xlToLeft/xlToRight/xlUp/xlDown -- the direction in which to find the end
Result: Range -- the cell at the given position
So the second line of the script would be:
set c to LastCell ur Direction xlDown
which maybe is clearer to read and understand, uncommented, too. (But some day you might want something else from the SpecialCells command, so that's why I used that one earlier).
** You say that Excel X AppleSript works fine. Yes it does, unlike Word X which crashes all the time and gets wrong results too before crashing, unless you know how to use 'do Visual Basic', and unlike PowerPoint X which has nothing but 'do Visual Basic'. But Excel 2004's dictionary is about 3 times as big – there's much more you can do there than in X. The dictionary was completely re-written. Even better, Microsoft has provided a 462-page Excel AppleScript Reference guide (the one for Word is 526 pages) at its Mactopia website (http://www.microsoft.com/mac/, click links to Resources/Developer/AppleScript), which really helps explain a lot of the obscure syntax. (Also in XL 2004, the syntax at least looks more like regular AppleScript syntax with separate words in lower case, even if it still reads very strangely).
(If and when you upgrade to Office 2004, make sure you first save copies of your Excel X scripts as text. Once you've launched Excel 2004, your compiled X scripts will open with indecipherable «raw codes» but text copies will read as you're accustomed to. It's then pretty easy to convert them to the new 2004 syntax which is quite close to the old syntax but with separate words not SquishedUpLikeThis, and they will then compile.)
--
Paul Berkowitz
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Applescript-users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden