【Excel VBA】よく使うセル範囲を動的に取得する方法を紹介【備忘録】

今回はよく使っているセル範囲を動的に取得する方法を紹介したいと思います。色々と応用が効くので、是非参考にしていただければと思います。

過去にVBAに関する記事を投稿していますので、是非こちらも併せてご覧ください！

セル範囲を動的に取得する方法

セル範囲を動的に取得する際、主に

の2つの方法で取得するようにしています。

それぞれ何がどう違うんだろう？

という方もいらっしゃると思うので、それぞれざっくり紹介したいと思います。

ちなみに、今回は以下のような表を使用して説明します。

セル範囲を動的に取得する方法の紹介の前に、Rangeの引数が複数個ある場合の見方をざっくり説明したいと思います。

まず、Rangeは単一のセルを指定できるだけでなく、複数のセルを指定することもできます。

単一のセルを指定するときは「Range(“A1”)」のように“”(ダブルクォーテーション)内に指定したいセルだけ記述しますが、複数のセルを指定する場合は

のどちらかで指定します。こうすることで複数のセルを指定することができます。

Range(“このセルから:このセルまで”)みたいなイメージで指定する感じです。

しかし上の方法だと指定したいセルがあらかじめ決まっている場合でないと使えません。

初めのうちはデータ件数が5件だったけど、後から10件に増えてしまった・・・

みたいな状況になるとその都度Range(“A1:A5”)→Range(“A1:A10”)のようにコードを書き直さないといけないはめになってしまいます。これでは流石に時間がもったいない上、何より面倒くさいですよね。

じゃあ動的に取得するにはどうするか、というと今回紹介する「Rangeの中身をCellsで指定して、セル範囲をデータ件数に応じて変える」方法になります。

まず一番よく使っているRows.CountとEnd(xlUp)を組み合わせて取得する方法です。コードは以下の通りです。

Sub select_Cells_01()
    With ThisWorkbook.ActiveSheet
        .Range(.Cells(1, 1), .Cells(Rows.Count, 3).End(xlUp)).Select
    End With
End Sub

実行すると以下のように表全体が選択状態になります。

動作イメージは以下の通りです。

Cells(Rows.Count,3)で最終行(1048576行目)を指定→この時点ではCells(1048576,3)になる
.End(xlUp)でデータが入っているセルまで上方向に検索・移動→データがあるのが5行目なのでCells(5,3)になる
データが入ってあるセルまでをセル範囲としてSelect→最終的にRange(Cells(1,1),Cells(5,3))＝Range(“A1:C5”)が選択状態になる

この方法の特徴は「一度最終行に移動してからセル範囲を決める」ところにあります。そのため、表の途中で空欄になっているセルがあってもそのセルも取得できます。

Rows.CountとEnd(xlUp)を使った場合の問題点は、欲しいセル範囲より下にもデータが入っている場合、そのデータまで取得してしまう点です。

例えば今回のように表だけ取得したい時に、表より下に何らかのデータが入ったセルがある場合、そこのセルまで取得してしまいます。

なので、Rows.CountとEnd(xlUp)を使う場合は取得したい列内にはいらないデータは入力しないようにしましょう。

次にEnd(xlDown)を使って取得する方法です。コードは以下の通りです。

Sub select_Cells_02()
    With ThisWorkbook.ActiveSheet
        .Range(.Cells(1, 1), .Cells(1, 3).End(xlDown)).Select
    End With
End Sub

実行すると同じように表全体が選択状態になります。

動作イメージは以下の通りです。

Cells(1,3).End(xlDown)でデータが入っているセルまで下方向に検索・移動→最後のデータがあるのが5行目なのでCells(5,3)になる
データが入ってあるセルまでをセル範囲としてSelect→最終的にRange(Cells(1,1),Cells(5,3))＝Range(“A1:C5”)が選択状態になる

動作的には割とシンプルで、指定したセルから下方向にデータが入っているセルまで検索し、それまでのセル範囲を取得する感じです。