Office Scripts

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

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

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

Public Sub Sample()
  '列を指定して最終セル取得
  Dim rngLast As Excel.Range
  Const col As Integer = 3

  With ActiveSheet
    Set rngLast = .Cells(.Cells(.Rows.Count, col).End(xlUp).Row, col)
  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)で固まる可能性もあります。
あれやこれやと考えているうちにこんなに冗長になってしまいましたが、これは改善の余地がありそうです。

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

関連記事

関連記事

  1. Office Scripts

    [Office Scripts]Power Automateからスクリプトを動的に書き換えて実行する…

    ※記事中でも書いていますが、本記事はPower Automateのフロ…

  2. Office Scripts

    [Office Scripts]Power Automateのフローでパラメーターの受け渡しを行う方…

    下記記事でも書いていますが、Power Automateのフローでパラ…

  3. Office Scripts

    [Office Scripts]スクリプトを共有する方法

    Office Scriptsは、作成したスクリプトを組織内で他のユーザ…

  4. Office Scripts

    [Office Scripts]外部APIを呼び出す方法

    下記サイトにあるように、Office Scriptsでは外部ライブラリ…

  5. Office Scripts

    [Office Scripts]オートフィルタを設定する方法

    今回は、Office Scriptsでテーブルのオートフィルタを設定す…

  6. Office Scripts

    [Office Scripts]Power Automateからスクリプトを実行する

    下記Webサイトにある通り、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.

最近の記事

アーカイブ

RapidSSL_SEAL-90x50
PAGE TOP