今回はよく使っているセル範囲を動的に取得する方法を紹介したいと思います。色々と応用が効くので、是非参考にしていただければと思います。
過去にVBAに関する記事を投稿していますので、是非こちらも併せてご覧ください!
セル範囲を動的に取得する方法
セル範囲を動的に取得する際、主に
- Rows.CountとEnd(xlUp)を組み合わせて取得
- End(xlDown)を使用して取得
の2つの方法で取得するようにしています。
![](https://masashi-blog418.com/wp-content/uploads/2022/09/b0261.png)
それぞれ何がどう違うんだろう?
という方もいらっしゃると思うので、それぞれざっくり紹介したいと思います。
ちなみに、今回は以下のような表を使用して説明します。
![セル範囲を動的に取得する方法](https://masashi-blog418.com/wp-content/uploads/2022/09/image.png)
Rangeの指定の仕方
セル範囲を動的に取得する方法の紹介の前に、Rangeの引数が複数個ある場合の見方をざっくり説明したいと思います。
まず、Rangeは単一のセルを指定できるだけでなく、複数のセルを指定することもできます。
単一のセルを指定するときは「Range(“A1”)」のように“”(ダブルクォーテーション)内に指定したいセルだけ記述しますが、複数のセルを指定する場合は
- 「Range(“A1:A5”)」のように:(コロン)で区切る
- 「Range(“A1″,”A5”)」のように,(カンマ)で区切る
のどちらかで指定します。こうすることで複数のセルを指定することができます。
![](https://masashi-blog418.com/wp-content/uploads/2022/06/IMG_6448.jpg)
Range(“このセルから:このセルまで”)みたいなイメージで指定する感じです。
しかし上の方法だと指定したいセルがあらかじめ決まっている場合でないと使えません。
![](https://masashi-blog418.com/wp-content/uploads/2022/09/b0271.png)
初めのうちはデータ件数が5件だったけど、後から10件に増えてしまった・・・
みたいな状況になるとその都度Range(“A1:A5”)→Range(“A1:A10”)のようにコードを書き直さないといけないはめになってしまいます。これでは流石に時間がもったいない上、何より面倒くさいですよね。
じゃあ動的に取得するにはどうするか、というと今回紹介する「Rangeの中身をCellsで指定して、セル範囲をデータ件数に応じて変える」方法になります。
Rows.CountとEnd(xlUp)を組み合わせる方法
まず一番よく使っている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
実行すると以下のように表全体が選択状態になります。
![セル範囲を動的に取得する方法_Rows.CountとEnd(xlUp)を組み合わせる方法_1](https://masashi-blog418.com/wp-content/uploads/2022/09/image-1.png)
動作イメージ
動作イメージは以下の通りです。
- 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)を組み合わせる方法_動作イメージ_1](https://masashi-blog418.com/wp-content/uploads/2022/09/image-4.png)
問題点
Rows.CountとEnd(xlUp)を使った場合の問題点は、欲しいセル範囲より下にもデータが入っている場合、そのデータまで取得してしまう点です。
例えば今回のように表だけ取得したい時に、表より下に何らかのデータが入ったセルがある場合、そこのセルまで取得してしまいます。
![セル範囲を動的に取得する方法_Rows.CountとEnd(xlUp)を組み合わせる方法_問題点_1](https://masashi-blog418.com/wp-content/uploads/2022/09/image-2.png)
なので、Rows.CountとEnd(xlUp)を使う場合は取得したい列内にはいらないデータは入力しないようにしましょう。
![セル範囲を動的に取得する方法_Rows.CountとEnd(xlUp)を組み合わせる方法_問題点_2](https://masashi-blog418.com/wp-content/uploads/2022/09/image-3-1024x300.png)
End(xlDown)を使う方法
次にEnd(xlDown)を使って取得する方法です。コードは以下の通りです。
Sub select_Cells_02()
With ThisWorkbook.ActiveSheet
.Range(.Cells(1, 1), .Cells(1, 3).End(xlDown)).Select
End With
End Sub
実行すると同じように表全体が選択状態になります。
![セル範囲を動的に取得する方法_End(xlDown)を使う方法_1](https://masashi-blog418.com/wp-content/uploads/2022/09/image-1.png)
動作イメージ
動作イメージは以下の通りです。
- Cells(1,3).End(xlDown)でデータが入っているセルまで下方向に検索・移動→最後のデータがあるのが5行目なのでCells(5,3)になる
- データが入ってあるセルまでをセル範囲としてSelect→最終的にRange(Cells(1,1),Cells(5,3))=Range(“A1:C5”)が選択状態になる
動作的には割とシンプルで、指定したセルから下方向にデータが入っているセルまで検索し、それまでのセル範囲を取得する感じです。
![](https://masashi-blog418.com/wp-content/uploads/2022/06/IMG_6448.jpg)
指定のセルを選択し、キーボードの「Shift」+「Ctrl」を押しながら矢印キーを押すと同じ動作になります。
1行開ける等して欲しいデータといらないデータを分けておけば、同じ列にいらないデータが入っていても必要なデータだけ取得することが可能です。
![セル範囲を動的に取得する方法_End(xlDown)を使う方法_動作イメージ_1](https://masashi-blog418.com/wp-content/uploads/2022/09/image-5.png)
問題点
End(xlDown)を使った場合の問題点は、欲しいセル範囲内に空欄がある場合、その手前のデータしか取得できない点です。
例えば今回の表内に空欄がある場合、その空欄の手前までしかセルを取得できなくなってしまいます。
![セル範囲を動的に取得する方法_End(xlDown)を使う方法_問題点_1](https://masashi-blog418.com/wp-content/uploads/2022/09/image-6.png)
なので、End(xlDown)を使う場合は取得したいセル範囲内に空欄がないようにしましょう。
どっちを使うべきか
ここまで2つセル範囲を動的に取得する方法を紹介しましたが、
![](https://masashi-blog418.com/wp-content/uploads/2022/09/b0261.png)
結局どっちを使えばいいの?
となる方もいると思います。
私個人の結論としては「Rows.CountとEnd(xlUp)を組み合わせる方法」を使うのをおすすめします。
というのも
- 範囲内の途中で空欄があっても取得できる
- 基本的に集計対象のデータが行方向に増えるケースが多かった
からですね。
正直私自身そういったフォーマットに立ち会う機会が多かった為、必然的にこちらの方法を使うことが多かったです。
勿論End(xlDown)の方が適したフォーマットもあるので、どちらが用途に適しているか判断していただければと思います!
まとめ
今回はよく使っているセル範囲を動的に取得する方法を紹介しました。
分かりにくい所も多々あると思いますが、
- 途中に空欄があるデータも含めて取得したい→Rows.Count + End(xlUp)
- 同じ列の関係ないデータは含めず取得したい→End(xlDown)
という風にざっくり覚えておけばとりあえずOKです(笑)
データ集計などで使える内容になっていますので、是非覚えて活用していただければと思います!
また、VBAに関する投稿を随時更新していますので、こちらも是非ご覧ください!
コメント