Office Scripts

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

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

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


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 Scriptsでテーブルのオートフィルタを設定す…

  2. Office Scripts

    [Office Scripts]ワークシート関数を実行しようとして失敗した話

    下記記事でも紹介していますが、Office アドインではワークシート関…

  3. Office Scripts

    Office Scriptsの要件と制限

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

コメント

  • コメント (2)

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

    • AddinBox_Tsunoda
    • 2020年 6月 17日 3:39pm

    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.

※本ページはプロモーションが含まれています。

Translate

最近の記事

アーカイブ

PAGE TOP