(1) 最終行の取得とは
- 便宜上,列のなかでデータが格納されている最下のセルの行を最終行(EndRow),テーブルの最下行(LastRow)とする.
- 下の例では列Aの最終行(EndRow)は"12",最下行(LastRow)は"21".
- 上から順番に降りていくように,セルが空かどうか順に判定していく方法だと,行 "6" になってしいうまくいかない.そこで,一般的には列の最終行から上昇して,順にセルが空かどうか判定していく方法をとる.
- Exel-VBAの場合," .End(xlUp) "が準備されている[1].
- applescriptの場合," .End(xlUp) "に相当するものがない.なので作る.
(2) 準備
Prepare a table
tell application "Numbers" activate make document tell document 1 tell sheet 1 tell table 1 -- HERE INSERT CODE (see article below) end tell end tell end tell end tell
(3) 列Aの最終行を取得する
- 変数 theLastRowに列"A"の最下行を格納.
- 最終行は column "A" の "last cell" の "row" の "address"で取得できる.
- セルが空(missing value)の場合は上の行の判定にいき,空でなかったら場合はrepeatを抜ける."theLastRow - i "が最終行.
- 検証のためにアクティブセルをA列の最終行のセルにする.
Get The End Row Of Column A
set i to 0 set theLastRow to address of row of last cell of column "A" repeat while i ≤ theLastRow if missing value ≠ value of cell (theLastRow - i) of column "A" then exit repeat set i to i + 1 end repeat set selection range to cell (theLastRow - i) of column "A"
Translated To English
(1) Get The End Row
- In VBA, we can use " .End(xlUp) " to get the end row.
- In applescript, there isn't formula equal to ".End(xlUp) in VBA.
- So, we make script to get the end row.
(2) Preparation
(3) Script To Get The End Row Of Column "A"
- For convenience, the row of the most lower cell which has data in a column is named "EndRow", and the row of the most lower cell in a table is named "Last Row".
- Set variable "theLastRow" to the last row of column A.
- We can get the last row of column as "address" of "row" of "last cell" as "column "A"" .
- From bottom to top manner, check whether if the cell is empty or not in "repeat" block.
- "theLastRow - i " is "EndRow"