I have a script that will select alternating rows based on a beginning selection.
The problems is when the beginning selection is more than 53 rows, it will only process the first 53 rows.
use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
local myRange, rowSelect, sr, er
tell application "Microsoft Excel"
set myRange to selection
set sr to first row index of (row 1 of myRange)
set er to first row index of (row -1 of myRange)
display alert "End row = " & er
set rowSelect to range ("$" & sr & ":$" & sr)
repeat with i from (sr + 2) to er by 2
set rowSelect to (union range1 rowSelect range2 range ("$" & i & ":$" & i))
end repeat
select rowSelect
end tell
I’m assuming it’s yet another bug in AppleScripting in Microsoft Excel!
Kind of an odd endeavour but it works for me up to row 61.
I don’t know why it’s hitting that (or any) ceiling but perhaps it’s related to the union command having a limit of 30 range arguments (at least on Excel 2011). While you’re not processing it like that, maybe it’s how it’s dealt with under the hood.
For fun, take a look at the log entries once you get above the apparent limit.
Try something like this:
tell application "Microsoft Excel"
set myr to selection
get address of myr
set sr to first row index of (row 1 of myr)
set er to first row index of (row -1 of myr)
set oddList to ""
repeat with x from sr to er -- build string of rows, e.g. "1:1, 3:3, 5:5"
if x mod 2 is 1 then set oddList to oddList & x & ":" & x & ", " as text
end repeat
set oddList to text 1 thru -3 of oddList -- remove final comma and space
select range oddList
end tell
It will build a string of rows and then select them as a single range.
For me, it works for up to 1690 rows. After that, the script runs but the selection goes awry — it alternates up to row 15 and then selects every row through to ‘er’. It tends to do that when the selections get overly complex and large so if you need more than that, you will need different solution.
I changed a few things. But it now craps out at 57 rows
use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
local myRange, rowSelect, sr, er
set tid to text item delimiters
set text item delimiters to ", "
tell application "Microsoft Excel"
set myRange to selection
set sr to first row index of (row 1 of myRange)
set er to first row index of (row -1 of myRange)
set oddList to {}
repeat with x from sr to er by 2 -- build string of rows, e.g. "1:1, 3:3, 5:5"
set end of oddList to ("$" & x & ":$" & x)
end repeat
set oddList to oddList as text
select range oddList
end tell
set text item delimiters to tid
Works for me with 200 row range selected (A1:D200).
And after some more testing… out of curiosity, why do you need the ‘$’? When I add them in, I can only process 1430 rows (A1:D1430). Above that I get a -1728 error.
I should note that I looked at the size of the strings involved. I took the resulting ranges and then got the word count for each of them.
When I include the ‘$’, the file size is about 8kb (8196 characters) at 1430 rows.
When I exclude the ‘$’, the file size reaches 8kb at 1690 rows.
Perhaps a string size of 8kb is a hard limit — at least on my setup.
I guess I should ask why you wish to create such a selection in the first place?
I’ve noticed that when viewing ranges in “Script Debugger” all ranges and rows are of the format “$51:$51” for rows, “$A$1” for cells
If I remove the “$”, I get the same result
I think it’s a default. Many commands, such as get address return them naturally. The dollar sign makes the reference absolute. When there isn’t a dollar sign, the reference is relative.
You can control it in some commands like get address by adding row absolute as a boolean (or column).
get address of range rowsExistAddress without row absolute
--> "$A1:$A3"
So in this range, the columns are absolute but the rows are relative.
Unless your spreadsheet requires absolute references, I wouldn’t add the dollar signs.
NB I use ‘get address’ as an example because it allows you to change the result that other commands generate.