和風スパゲティのレシピ

日本語でコーディングするExcelVBA

セル範囲(Range)を指定する方法10選

この表のオレンジの部分を取得しましょう。


取得したいRangeの範囲

VBAをやっていて、この処理をしたことが無い人はいないのでは?
ってくらいよくある処理ですよね。


単純に書こうとすれば、初心者でもすぐ書けるような方法がいくらでもあるので、
普段はあまり考えない部分かもしれません。


ですがこの処理の書き方はいろいろなやり方があり、
コードの読みやすさの、1つのポイントになる部分です。


読みやすさ、メンテのしやすさ、バグの書きづらさなど、
追求しようと思えば、とても考えがいのある処理です。

自分の知識の確認の意味でも、ちょっと考察してみましょう。
 

 

Range("セルアドレス")で指定する

Range("E3:E7")

まずどストレートにこれ。基本中の基本ですね。


「初心者向け」と見せかけて、単純な読みやすさでは一番なので、
「完全に固定の範囲」であれば、上級者になっても普通に使うと思います。


Range(○○)はいろんな書き方ができますね。


やる人はいないと思いますが、

Range("E3,E4,E5,E6,E7")

でもいけます。


意外と知らない方もいるかもしれませんが、

Range("E3","E7")

でも同じエリアを取得できますし、

Range("E3:E4","E6:E7")

と、範囲から範囲まででも行けたりします。


まあこの辺は遊びというか、実際にこれらの指定方法で何かマクロを組んだことはほとんどないですね(笑)


使い道があるとすれば、「A1セルからこの表までの全体」を取得したいときに、
↑の「範囲から範囲もOK」パターンが使えて、

Range("A1", "A2:E7")

こう書けるくらいでしょうか。


ここを印刷範囲にしたり、.Rows.Countで最終行を取得したりはできますね。

Range("○○")を変数で指定できるようにする

取得したいRangeの範囲

先ほど「完全に固定の範囲」であればRange("E3:E7")も使うと書きました。


ですが、今回の範囲はどう見ても「完全に固定」ではないですね。
次のくだものが売れていかないと、お店はやっていけません。


プログラミングの極意は「動的なデータにどう対応するか」ですので、
まずは一番基本となる「データがどこまで入っているか」に対応しましょう。


ここから先は、「E7」ではなく、

Dim 最終行 As Long

という変数に最終行を取得して、それを使うことにします。


最終行番号の取得コードは、長くなるので別の機会に。

With ActiveSheet
    最終行 = .Cells(.Rows.Count, 5).End(XlUp).Row
    最終行 = .UsedRange.Rows.Count + .UsedRange.Row - 1
    最終行 = .Range("A1", .Autofilter.Range).Rows.Count
End With

↑どれでも好きなものをイメージして先に進んでください。


Range("セルアドレス")で動的データに対応する場合は、

Range("E3:E" & 最終行)

こんな感じで、文字列結合を利用することができます。


ひとまずこれで、データがどんどん追加されていくことには自動対応できますね。

Range(Cells(左上), Cells(右下))で指定する

さてここからはプログラムらしくなっていきます。

Range(Cells(3, 5), Cells(7, 5))

これでE3:E7になりますね。
セル範囲を数値で表そうと思ったら、これが一番基本だと思います。


もちろんこんな数字ベタ打ちでは何の意味もなく、
「目的は変数・定数を使えるように」です。


変数と定数を使った例がこんな感じです↓

取得したいRangeの範囲

' モジュール上部にて
Const R1stくだもの表 = 3
Const CNoくだもの表_No = 1
Const CNoくだもの表_品物 = 2
Const CNoくだもの表_単価 = 3
Const CNoくだもの表_数量 = 4
Const CNoくだもの表_売上 = 5

' コード内にて
Dim RLastくだもの表 As Long
RLastくだもの表 = さっきイメージした最終行取得コード

' E3:E7を取得するコード
Range(Cells(R1stくだもの表, CNoくだもの表_売上) _
        , Cells(RLastくだもの表, CNoくだもの表_売上))

これで、4つの数字を全部定数・変数にできました。


最後の式に値を代入してみれば、

Range(Cells(3, 5), Cells(7, 5))

になります。


ここまでくれば、「データがどこまで入っているか」という実行時の対応だけでなく、

  • 表の上部に余白の行を挿入したい
  • 品物と単価の間に購入日の列を挿入したい

といった、表自体の改修時にも、定数をいじるだけで対応できるようになります


ひとまず、「Rangeプロパティで表のデータ部分をもって来よう」というマクロを、
メンテナンス性を意識して書く場合は、これが基本の形だと思います。



まあここまでやるかどうかは、マクロの規模やデータの重要度と相談してください。


単発の業務で使う表なら、

Range("E3:E" & 最終行)

でも十分動的です。


逆にいろいろな業務で共有する重要なデータなどでは、
この「売上を意味するE」が、たくさんのマクロに登場するでしょう。

↑の書き方では、列を挿入して売り上げがF列になったときに地獄を見ますが、

定数化している場合は

Const CNoくだもの表_売上 = 5

この5を6に変えるだけでマクロの改修が完了します。

この辺を吟味して、どこまで丁寧にデータを定数化するかを決めてください。

ちょっと寄り道:Enum定数で列番号を定義

先ほどの定数で、列番号を定義していた

Const CNoくだもの表_No = 1
Const CNoくだもの表_品物 = 2
Const CNoくだもの表_単価 = 3
Const CNoくだもの表_数量 = 4
Const CNoくだもの表_売上 = 5

この部分は、列挙型定数Enumを用いて、

Enum CNoくだもの表
    No = 1
    品物   ' ここから↓が2,3,4…と連番で定義されるしくみ
    単価
    数量
    売上
End Enum

これで書き換えることができます。


その場合のRangeの指定はこんな感じ。

Range(Cells(R1stくだもの表, CNoくだもの表.売上) _
        , Cells(RLastくだもの表, CNoくだもの表.売上))

 
列挙型Enumは、単にLong型の定数をまとめて持っておく機能ですが、
「番号を省略時は自動で連番」という仕様が、列番号の定数化に超便利です。


先ほど例に挙げた「品物と単価の間に購入日の列を挿入」する場合、

Enum CNoくだもの表
    No = 1
    品物
    購入日   ' これを書き加えると、
    単価      ' ここから↓が1ずつ足される神機能
    数量
    売上      ' さっきの例の通り、5が6になっている
End Enum

これで済むという、ある意味列番号を定義するために生まれてきたやつです。

Enumを初めて知った方は、この機会に覚えてしまいましょう。

Resize(データ数)で取得する

取得したいRangeの範囲

さて、定数化がようやく終わりましたので、
やっとこさ本題のRange取得プロパティを紹介できますね(;´∀`)


これから紹介していくプロパティの使い方では、
「定数と相性が良いか」も重要な要素になるため、
どうしても飛ばせなかったんです。ご勘弁を。


ということでここからはサクサク行きましょう。
まずはResizeプロパティから。

' データの数を計算しておく
Dim データ数 As Long
データ数 = RLastくだもの表 - R1stくだもの表 + 1

' E3:E7はこれ
Range("E3").Resize(データ数)

' 定数を使うならこう
Cells(R1stくだもの表, CNoくだもの表.売上).Resize(データ数) 


Resize(行数,列数)でセル範囲を拡張できます。
列数は省略したので元の列幅を維持した1になります。

一番目のセルからデータ数だけ下へ伸ばした範囲」という書き方が、
直感的にとてもわかりやすいため、素晴らしく読みやすいコードになります。


しかも一度取得した「データ数」は使いまわせるため、

みかんの合計 = WorksheetFunction.Sumif _ 
    (Range("B3").Resize(データ数), "みかん", Range("E3").Resize(データ数))

のように、SUMIF(B列,みかん,E列)がこんなに読みやすく書けます。


Resizeを使いこなせると、Rangeの扱いにかなり幅が出ますので、
是非習得していってください。


例えば表全体の範囲なんかも、

Range("A3").Resize(データ数, 最終列)

で取得することができます。

Offsetで取得する

Range("D3:D7").Offset(0, 1)

Resizeの次は、親戚のOffsetを紹介。


Rangeオブジェクト.Offset(行数, 列数)で、
Rangeオブジェクトを「同じ大きさのまま指定の数分ズラす」ことができます。


今回の例では、「数量列のひとつ右」でE3:E7を取得しています。


大きさが変わらないというのがOffsetのいいところで、
例えばさっきのSUMIFを例にとると、

Set 売上列 = Range("E3").Resize(データ数)

みかんの合計 = WorksheetFunction.Sumif(売上列.Offset(0, -3), "みかん", 売上列)

こんな書き方をすることができます。

読みやすくはないですが、書くのは速そうですね。


定数が使えていないことからもわかる通り、
Offsetは書きやすいけど読みにくいプロパティです。


Resizeと組み合わせると、変幻自在にRangeを扱えるようになりますが、
その自在っぷりに未来の自分が惑わされる可能性があります。

ご利用は計画的に。

表全体の範囲から、列部分を切り取って取得する

取得したいRangeの範囲

今まで挙げた例は、E3からE7までをくっつけたり、動かしたりといった、
いわば「積み木」のような取得方法でした。


これとは異なるアプローチのセル範囲の取得方法として、
「表全体という板から、E列を切り取る」
という、のこぎり的な方法もありますのでご紹介します。

Columnsで取得する

表全体を表すRange("A3:E7")を使うと、こんな感じに書くことができます。

Range("A3:E7").Columns("E")
Range("A3:E7").Columns(CNoくだもの表.品物)

 
Columnsプロパティは、よくある「Worksheet」を親にすると列全体を指します。
省略時はActiveSheetの列全体を取ってきますよね。


このColumnsプロパティですが、実はRangeオブジェクトを親にすることもでき、
「セル範囲が入っているRangeオブジェクト.Columns(列番号)」とすると、
「そのセル範囲の第○列目」を取ってくることができます


書いたコードの見た目も、「くだもの表の品物列」って感じで、とてもいいですね。


ただしこの方法、データがA列から始まっていると、
かなり読みやすくていい方法なんですが、

Columnsの罠

この表の場合は注意が必要です。

この場合、

Range("B3:E7").Columns("E")

これはシート上で言うところのF列を指してしまいます

B列からみたE列のようなイメージになっちゃうため、
A列始まりでない表では、その分ズレちゃうんですよね…。


ということで、Columnsの性質を覚えつつ、本命は↓へ。

Intersectメソッドで取得する

Intersectメソッドは、
引数に渡すセル範囲たちが「重なっている部分」を返すメソッドです。

今回の場合は、「表全体」と「列全体」の重なる部分を取ればいいですね。


Intersectの例

こんな感じで範囲を取得します。「交差部分」と呼んだりしますね。


これなら表エリアの開始列がA列かどうかに関わらず、
「第5列」なら5で行けますので、定数との相性も良いです↓

Intersect(Range("B3:E7"), Columns(CNoくだもの表.売上))

 

私はEnumを愛しているので、Enumと相性のよいこれが一番好きですね。

表全体を簡単に取得するプロパティ

さて、わざわざこれらの「のこぎり」を便利と紹介したのは、
元の板となるRange("A2:E7")を、簡単に取得するいいプロパティがあるからです。


まずは以下の2つを覚えておきましょう。

CurrentRegionプロパティ

あるセルを含む、表っぽい部分を自動取得してくれます。

CurrentRegionのサンプル

この機能は「アクティブセル領域」と呼び、
「Ctrl + Shift + :」 にショートカットがセットされていますので、
試しにやってみてください。


空の行だったり、表が複数隣接していない限りは、
手っ取り早く表のセル範囲を取得できる優秀なプロパティです。


今回の例では、

Range("A2").CurrentRegion

でRange("A2:E7")を取得できます。


というのは嘘で、Range("A1:E7")を取得しやがります。

アホの子Region

大変便利なCurrentRegionさんですが、
今回のように、「表のタイトル」などをよくアホの子しますので、
ご利用は計画的に。

ワークシート.AutoFilter.Range

そのまんま、オートフィルターのエリアを取得します。


親のオブジェクトが「ワークシート」なので、
どこか起点となるセルを探さなくてもいいのがメリット。
 

Worksheets("くだもの売上表").AutoFilter.Range

これで"A2:E7"を取得できます。

セル番地が一つも出て来ないコードにできます。


ユーザー側で設置しちゃえるので、Excelがアホの子しないのもメリットですね。
(ユーザーがアホの子する危険がありますが)


今回のように、キレイにフィルターがかかっているなら、こっちを使いましょう。

見出しがいらない

取得したいRangeの範囲

さて、さっきからA2:E7を取得して喜んでいますが、
みなさん薄々感じています通り、本当に欲しいのは実はA3:E7です。

ということで、「見出しの1行を範囲から外す」方法が必要です。


が、残念ながらこの「範囲を縮小」、
一発で済むプロパティが用意されていません。


なので、今までの方法を組み合わせて、こうやります↓

Set データエリア = ActiveSheet.AutoFilter.Range
Set データエリア = データエリア.Offset(1).Resize(データエリア.Rows.Count - 1)

 
理屈は簡単。

エリアを取得後にOffset(1)で1つ下にズラして見出しを消した後、
おしりがはみ出た分をResize(データの行数-1)で1行減らして完了です。

知っていれば簡単に済みますが、思いつくのは大変ですね。
このOffset + Resize によるエリアの縮小も覚えてしまいましょう。

完成形

これでやっと完成ですね。

Dim データエリア As Range
Set データエリア = ActiveSheet.AutoFilter.Range
Set データエリア = データエリア.Offset(1).Resize(データエリア.Rows.Count - 1)

' E3:E7はこう書ける
データエリア.Columns(CNoくだもの表.売上)

' 表がA列スタートでないならこっち
Intersect(データエリア, Columns(CNoくだもの表.売上))


だいぶ苦労しましたが、それに見合う「読みやすいコード」になりました。

今までで一番、「データ表内の売上の列」を指している感が出ましたね。


これなら、たとえば売上の合計を求めたい時も、

WorksheetFunction.Sum(Intersect(データエリア, .Columns(CNoくだもの表.売上)))

こんな風にノーコメントでも、ぱっと見で「なにやってるかわかる」コードになります。



すごく長くなってしまいましたが、ここまでマスターできれば、
表形式のデータを扱うのが、だいぶやりやすくなるはずです。


Rangeは奥が深いですが、ExcelVBAの主役と言っていいオブジェクトです。
これから長い付き合いになりますから、仲良くVBAライフを歩んでいってくださいね。

おまけ:学んだコードは関数化してとっておく

こんなに長い記事を読んでくださいまして、ありがとうございました。

初級者の方は、初めてみるプロパティがたくさんあったかもしれません。


さて、実際使うとなると、いきなり暗記なんか無理です。

えー、じゃあまたこの長い記事を読み直すのか…。



と、そうならないために、こういうよく使うプロパティは、
簡単な関数にしてしまうという手があります。


関数に慣れるまではちょっと難しいかもしれませんが、

' オートフィルターのデータエリアの取得
Function GetRangeオートフィルターのデータエリア(対象シート As Worksheet) As Range
    With 対象シート.AutoFilter.Range
        Set GetRangeオートフィルターのデータエリア = .Offset(1).Resize(.Rows.Count - 1)
    End With
End Function

' オートフィルターのデータ列の取得
Function GetRangeオートフィルターの列データ(対象シート As Worksheet, 指定列番号 As Long) As Range
    Set GetRangeオートフィルターの列データ _ 
        = Intersect(GetRangeオートフィルターのデータエリア(対象シート) _
                          , 対象シート.Columns(指定列番号))
End Function


こうやって、ネットで見つけたプロパティを単純な関数にしておくと、
以降はこの関数を見ればよくなりますね。


もちろんコードを書くときにも、この関数を呼ぶだけでよくなるので、
プロパティなどをきっちり覚えなくてOK。


例えば、

取得したいRangeの範囲

この表で「みかんの売上の合計」を取得したい場合は、

WorksheetFunction.Sumif _
    (GetRangeオートフィルターのデータ列(ws集計シート, CNoくだもの表.品物), "みかん" _
   , GetRangeオートフィルターのデータ列(ws集計シート, CNoくだもの表.売上)

これで済みます。

読みやすいし、今日習ったプロパティ一個も書かなくてもいいし、
関数(プロシージャ)分割の練習にもなるし、言うことなしですね。


関数を作るのは最初は難しいかもしれませんが、
簡単な汎用関数で慣れておくと、複雑な処理を関数でパーツ分けするときの予習になります。

是非トライしてみてください。



ということで、ここまで読んでくださったお礼に、この関数をプレゼントします。


もうこのページに来ていただけないのは寂しいですが、
あなたの幸せなVBAライフを、草葉の陰から見守っていますよ(´∀`)

おまけ:1つ忘れていたメソッドを付け足し

この長ーい記事を校正していたら、有名なやつを1つ忘れてたのを思い出しました。


E3:E7は↓これでも取得できます。

Union(Range("E3:E4"), Range("E5:E7"))

範囲同士をくっつける、Unionメソッドです。


今回の例では全く役に立ちませんが、
便利な時もたまにあるので、頭の片隅にでも置いておいてください。



やっとこさ書き上げて、いまさらどこに追記すりゃええねん。


と思ったのですが、
このおまけに付け足した感じがまさしくUnionです。

覚えやすそうで気に入ったので、このままここに書いておきますね(´∀`)