Good to know.
I just own a PDF describing AppleScript for Excel 2004 and saw no reference to « delete row ».
Deleting starting from the bottom was a logical choice.
I forgot the ability to stop screen updating.
I used it in AppleWorks but as it's unavailable in Numbers my memory dropped it.
tell application "Numbers"
# minimize the active document
my dockIn("Numbers")
(*
# Do something in the document (can't be GUI Scripting)
tell document 1 to tell sheet 1 to tell table 1
repeat with r from 40 to 4 by -3
remove row r
end repeat
end tell
*)
# reveal the active document
my dockOut("Numbers")
end tell
#=====
on dockIn(theApp)
tell application theApp to activate
tell application "System Events" to tell (first process whose visible is true)
keystroke "m" using command down
end tell
end dockIn
#=====
on dockOut(theApp)
tell application theApp
activate
set theVersion to get version
end tell
# CAUTION : these instructions must be adjusted according to the triggered application
if theVersion < "3" then
set mt to 10
else
set mt to 11
end if
tell application "System Events" to tell (first process whose frontmost is true) to tell menu bar 1
# get name of menu bar items
# with Numbers 2.x
--> {"Apple", "Numbers", "Fichier", "Édition", "Insertion", "Tableau", "Format", "Disposition", "Présentation", "Fenêtre" (10), "Partage", "Aide"}
# with Numbers 3.1
--> {"Apple", "Numbers", "Fichier", "Édition", "Insertion", "Tableau", "Format", "Disposition", "Présentation", "Partager", "Fenêtre" (11), "Aide"}
tell menu bar item mt to tell menu 1
set mCount to count (get name of menu items)
repeat with m from mCount to 1 by -1
# exit the loop when we reach the active document's name
if value of attribute "AXMenuItemMarkChar" of menu item m is not missing value then exit repeat
end repeat
# make the document visible
click menu item m
end tell -- menu bar…
end tell -- System Events
end dockOut
#=====
Yvan KOENIG (VALLAURIS, France) lundi 24 février 2014 18:21:54
Le 24/02/2014 à 16:59, Paul Berkowitz <
email@hidden> a écrit :
You can go backwards, as you recommend, through each row of 'used range' , using
delete range row x shift shift up
if you find row x to be empty. (You also may have to ensure that you are deleting row x of the worksheet, not row x of the used range - I don't know. Perhaps not.)
You should also, after experimenting and finding if the script works,
set screen updating to false
before you start, and then et it to true at the end of the script. You then won't see the script removing each row one at a time, only all at once, but it should be much faster.
I haven't tested this method, but it's worth trying. I looked to see if there might be some "magic command" in Excel that deletes all unused rows in a range and avoids a repeat loop, but I couldn't find one.
--
Paul Berkowitz
From: "koenig.yvan" <email@hidden>
Date: Mon, 24 Feb 2014 14:18:26 +0100
To: AppleScript-Users <email@hidden>
Subject: Re: How can I delete every empty row of an Excel document?
I don't own Excel but I'm puzzled;
My understanding is that your code replace formulas by their result.
(1) Am'I right ?
(2) If I am right, is it an acceptable behavior ?
I was said that Excel dictionary is huge.
So I'm wondering if there is a way to scan the used range starting from the bottom and delete empty rows ?
If there is not, maybe you may use an empty column and
fill its cells with the row num if the row is not empty
leave the cells empty when the row is empty.
After that, sort the table according to this column to push the empty rows to the bottom
At last, clear the cells of the column.
Yvan KOENIG (VALLAURIS, France) lundi 24 février 2014 14:17:26