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

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

Office Scripts

2021.04.14

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

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

2021.07.05

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

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

Office Scriptsでセル移動(Ctrl + 矢印キー)とセル範囲選択(Ctrl + Shift + 矢印キー)ができるようになりました。

	//セル移動とセル範囲選択
	function main(workbook: ExcelScript.Workbook)
	{
	const sheet = workbook.getActiveWorksheet();
	const range = sheet.getRange("A1");

	//セル移動
	//任意のセルから指定した方向に移動した先のセルを返す。
	//ショートカットキー：Ctrl + 矢印と同じ
	console.log(range.getRangeEdge(ExcelScript.KeyboardDirection.down).getAddress());

	//セル選択
	//任意のセルから指定した方向のデータが途切れるセルまでのセル範囲を返す。
	//ショートカットキー：Ctrl + Shift + 矢印と同じ
	console.log(range.getExtendedRange(ExcelScript.KeyboardDirection.down).getAddress());
	}

view raw セル移動とセル範囲選択.ts hosted with

by GitHub

(Range).getRangeEdgeメソッドがセル移動、(Range).getExtendedRangeメソッドがセル範囲選択です。

個人的にはすごく待ちわびていた機能追加で、これらのメソッドによって終端のセルを簡単に取得できるようになったわけです。
(VBAでいうと下記ページのような実装です。)

support.microsoft.com

Visual Basic for Applications で範囲を選択する方法 (初心者向けのサンプル)

https://support.microsoft.com/ja-jp/topic/cfc560b1-3dfc-bda7-a0da-a3cbed6abcab?WT.mc_id=M365-MVP-4029057

ただ、連続したデータ範囲の場合は下方向の指定で問題無いのですが、途中で空白セルが含まれる場合は終端のセルを取得できません(下記参照)。

	function main(workbook: ExcelScript.Workbook)
	{
	const sheet = workbook.getActiveWorksheet();

	//連続したデータ範囲の場合は下方向の指定で問題無し
	console.log(sheet.getRange("E1").getRangeEdge(ExcelScript.KeyboardDirection.down).getAddress());

	//途中に空白セルが含まれる場合は終端セルを取得できない
	console.log(sheet.getRange("A1").getRangeEdge(ExcelScript.KeyboardDirection.down).getAddress());
	}

view raw getRangeEdgeメソッドによる下方向指定.ts hosted with

by GitHub

そこで通常は下記コードのように最終行のセルから上方向を指定してセルを取得するのですが、Excel 2007でシートの最大行数が変更された過去もありますし、「A1048576」のように最終行を決め打ちするのは控えておきたいところです。

	function main(workbook: ExcelScript.Workbook)
	{
	const sheet = workbook.getActiveWorksheet();

	//最終行のセルから上方向指定
	console.log(sheet.getRange("A1048576").getRangeEdge(ExcelScript.KeyboardDirection.up).getAddress());
	}

view raw 最終行のセルから上方向指定.ts hosted with

by GitHub

VBAでは下記コードのようにRows.CountとEndプロパティを使えば済む話なのですが、Office Scriptsでは同様のプロパティを見つけられませんでした。
改めて考えたらgetRowCountメソッドを使えば良いことに気が付きました。コードはページ下部の「指定した列の最終セルを取得する方法(2)」参照。

Public Sub Sample()
  With ActiveSheet
    Debug.Print .Cells(.Rows.Count, 1).End(xlUp).Address
  End With
End Sub

仕方がないので、(Worksheet).getUsedRangeメソッドを使って、使用しているセル範囲の中から指定した列の最終セルを取得するコードを書いてみました。

	function main(workbook: ExcelScript.Workbook) {
	const range = getLastRange(workbook.getWorksheet("Sheet1"), "A");
	console.log(range.getAddress());
	}

	//最終セル取得
	function getLastRange(sheet: ExcelScript.Worksheet, column: string): ExcelScript.Range {
	let lastRange = sheet.getUsedRange()?.getIntersection(`${column}:${column}`)?.getLastCell() ?? sheet.getRange(`${column}1`);
	if (lastRange.getRowIndex() > 0 && lastRange.getValue() === "") {
	lastRange = lastRange.getRangeEdge(ExcelScript.KeyboardDirection.up);
	}
	return lastRange;
	}

view raw 指定した列の最終セルを取得する方法.ts hosted with

by GitHub

実は、同様の処理は一年ほど前の下記記事でも書いているのですが、getRangeEdgeメソッドのおかげでかなり短くなり、さらに処理速度も早くなっています。

2020.06.17

[Office Scripts]列を指定してデータが入っているセル範囲の最後のセルを取得する方法

2021/4/14 追記： getRangeEdgeメソッドが追加されたので短いコードで処理できるようになりました。 2021/7/5 追記：さらに簡単に指定した列の最終セルを取得する方法について記事を書きました。 E...

どんどん便利になっていくOffice Scripts、これからも要注目です！

2021/4/19 追記：

	function main(workbook: ExcelScript.Workbook)
	{
	const range = getLastRange(workbook.getWorksheet("Sheet3"), "A");
	console.log(range.getAddress());
	}

	//最終セル取得
	function getLastRange(sheet: ExcelScript.Worksheet, column: string): ExcelScript.Range {
	const lastRow = sheet.getRange(`${column}:${column}`).getRowCount();
	let lastRange = sheet.getRange(`${column}${lastRow}`);
	if (lastRange.getValue() === "") {
	lastRange = lastRange.getRangeEdge(ExcelScript.KeyboardDirection.up);
	}
	return lastRange;
	}