2021/4/14 追記：
getRangeEdgeメソッドが追加されたので短いコードで処理できるようになりました。

2021.04.14

[Office Scripts]指定した列の最終セルを取得する方法

2021/7/5 追記： さらに簡単に指定した列の最終セルを取得する方法について記事を書きました。 Office Scriptsでセル移動(Ctrl + 矢印キー)とセル範囲選択(Ctrl + Shift + 矢印キー)ができるようにな...

2021/7/5 追記：
さらに簡単に指定した列の最終セルを取得する方法について記事を書きました。

2021.07.05

[Office Scripts]指定した列の最終セルを取得する方法(2)

Office スクリプトで指定した列の最終セルを取得する方法として、これまでgetSpecialCellsメソッドを使ったりgetRangeEdgeメソッドを使ったり色々と試行錯誤をしていたわけですが、今日Stack Overflowを見ていて、もっと簡単な方...

Excelの処理を自動化する際、データが入っているセル範囲の中の最後のセル(＝最終セル)を取得し、その一つ下のセルから入力を行いたい場合が多々あります。

VBAなら下記のように「End」プロパティを使うのが鉄板ですね。ほんの数行で最終セルが取得できます。

ところが、Office ScriptsではこのEndプロパティ(あるいは類するプロパティやメソッド)が見つかりません。

私の探し方が悪いだけかもしれませんが、「操作の記録」でもセル移動は記録されませんでした。

仕方が無いので、どうすれば列を指定して“データが入っているセル範囲の最後のセル”を取得できるのかを試行錯誤してみました。

getRangeメソッドとgetLastCellメソッド

Rangeオブジェクトには、セル範囲内の最後のセルを取得するgetLastCellメソッドが用意されています。

ところが、下記コードのように、列を指定してセル範囲を取得す(Worksheet).getRangeメソッドと組み合わせた場合、データが入っているセルとは関係無く、指定した列の最後のセルが取得(下記の場合、セルA1048576)されるため、目的を達成できません。

	//A列の最後のセルを選択
	function main(workbook: ExcelScript.Workbook) {
	let sheet = workbook.getActiveWorksheet();
	let lastRange = sheet.getRange("A:A").getLastCell();
	lastRange.select();
	}

view raw A列の最後のセルを選択.ts hosted with by GitHub

getUsedRangeメソッドとgetIntersectionメソッド

Worksheetオブジェクトには、データが入っているセル範囲を取得するgetUsedRangeメソッドが用意されています。

このメソッドと(Range).getIntersectionメソッドを組み合わせることで、“データが入っているセル範囲の中の指定した列”のセル範囲を取得できますが、この列の最終セルが“データが入っている最後のセル”であるとは限りません。

	//UsedRange範囲内でA列最後のセルを選択
	function main(workbook: ExcelScript.Workbook) {
	let sheet = workbook.getActiveWorksheet();
	let usedRange = sheet.getUsedRange();
	let intRange = usedRange.getIntersection("A:A");
	let lastRange = intRange.getLastCell();
	lastRange.select();
	}

view raw UsedRange範囲内でA列最後のセルを選択.ts hosted with by GitHub

getSpecialCellsメソッドとgetRowsAboveメソッド

Rangeオブジェクトには、指定した種類のセル範囲コレクションを取得するgetSpecialCellsメソッドが用意されています。

このメソッドを使うことで、データが入っていない空白のセル範囲をコレクションとして取得し、コレクションの一番最後のセル範囲、その一つ上のセルをgetRowsAboveメソッドを使って取得することで、指定した列の最後のデータが入ったセルを取得できます。

	//A列最後のデータが入ったセルを選択
	function main(workbook: ExcelScript.Workbook) {
	let sheet = workbook.getActiveWorksheet();
	let usedRange = sheet.getUsedRange();
	let intRange = usedRange.getIntersection("A:A");
	let blankRangeAreas = intRange.getSpecialCells(ExcelScript.SpecialCellType.blanks);
	let blankRange = blankRangeAreas.getAreas()[blankRangeAreas.getAreaCount() - 1];
	let lastRange = blankRange.getRowsAbove(1);
	lastRange.select();
	}

view raw A列最後のデータが入ったセルを選択.ts hosted with by GitHub

図で示すと下図のようになります。

まとめ

これまでの処理をまとめると、下記のようなコードになりました。

	//列を指定してデータが入っているセル範囲の最後のセルを取得
	function main(workbook: ExcelScript.Workbook) {
	const column: string = "A";
	let lastRange: ExcelScript.Range;
	let sheet = workbook.getActiveWorksheet();
	let usedRange = sheet.getUsedRange();
	if (usedRange !== void 0) {
	let intRange = usedRange.getIntersection(`${column}:${column}`);
	if (intRange !== void 0) {
	lastRange = intRange.getLastCell();
	if (lastRange.getValue() === "") {
	let blankRangeAreas = intRange.getSpecialCells(ExcelScript.SpecialCellType.blanks);
	let blankRange = blankRangeAreas.getAreas()[blankRangeAreas.getAreaCount() - 1];
	if (blankRange.getRow(0).getRowIndex() === 0) {
	lastRange = sheet.getRange(`${column}1`);
	} else {
	lastRange = blankRange.getRowsAbove(1);
	}
	}
	}
	}
	if (lastRange === void 0) lastRange = sheet.getRange(`${column}1`);
	//最終セルの1つ下のセルから処理
	let startRange: ExcelScript.Range = lastRange;
	if (startRange.getRowIndex() > 0) startRange = startRange.getRowsBelow(1);
	startRange.select();
	}

view raw 列を指定してデータが入っているセル範囲の最後のセルを取得.ts hosted with by GitHub

長い！！

しかも数万行使っているシートだと空白セルの取得時点(getSpecialCells)で固まる可能性もあります。
あれやこれやと考えているうちにこんなに冗長になってしまいましたが、これは改善の余地がありそうです。

まあ、こんなコードでもどなたかのご参考になれば幸いです。

関連記事

2020.05.03

Office Scripts(Office スクリプト)の記事まとめ

当ブログの「Office Scripts」関連の記事をまとめてみました。 Office Scriptsの基本 セル操作 シート操作 図形・画像操作 テーブル操作 グラフ操作 Power Automate連携 その他 O...