Office Scripts

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

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


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

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

Public Sub Sample()
  '列を指定して最終セル取得
  Dim rngLast As Excel.Range
  Const col As Integer = 3
 
  With ActiveSheet
    Set rngLast = .Cells(.Rows.Count, col).End(xlUp)
  End With
  
  '最終セルの1つ下のセルから処理
  Dim i As Integer
  If Not IsEmpty(rngLast) Then Set rngLast = rngLast.Offset(1)
  For i = 1 To 10
    rngLast.Offset(i - 1).Value = i
  Next
End Sub

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

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

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

getRangeメソッドとgetLastCellメソッド

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

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

getUsedRangeメソッドとgetIntersectionメソッド

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

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

getSpecialCellsメソッドとgetRowsAboveメソッド

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

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

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

まとめ

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

長い!!

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

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

関連記事

Google Apps Script 活用トーク #6に参加しました – #GAS活前のページ

Nintendo Switchソフト「SELFY COLLECTION 夢のスタイリスト」がめっちゃ可愛かったのでパケ買いした件次のページ

関連記事

  1. Office Scripts

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

    当ブログの「Office Scripts」関連の記事をまとめてみました…

  2. Office Scripts

    Office Scriptsの要件と制限

    いまさらですが、Office Scriptsのシステム要件や制限事項に…

  3. Office Scripts

    Microsoft 365 Virtual Marathon 2021でOffice Scripts…

    先月末(日本時間だと4月27日 ~ 29日にかけて)にMicrosof…

  4. Office Scripts

    Office Scriptsの使いどころ

    これまで当ブログでOffice Scriptsについて色々と記事を書い…

  5. Office Scripts

    [Office Scripts]シートの取得方法まとめ

    今回は、Office Scriptsの基本となるシートの取得方法をまと…

コメント

  • コメント (2)

  • トラックバックは利用できません。

    • AddinBox_Tsunoda
    • 2020年 6月 17日

    OpenOffice.org ( LibreOffice / AppacheOffice ) の Basic と同じだぁ~
    http://blog.livedoor.jp/addinbox/archives/51212979.html

    「VBAだと、こんなに面倒くさいのが、これだけで済むの! ( ̄Δ ̄;)」というのもあれば
    「VBAだと、こんなに簡単で済むのに~ (ToT) 」というのもある・・・

    • 角田さん

      ご無沙汰しております。
      記事拝見しましたが、OpenOfficeもこれまた面倒ですねΣ(゚ロ゚;)

      Office Scriptsも、私が知らないだけで何か他にもっと良い方法あるんじゃないのかなー?とは思うのですが・・・(@_@;)

Time limit is exhausted. Please reload CAPTCHA.

最近の記事

アーカイブ

PAGE TOP