この表のオレンジの部分を取得しましょう。
VBAをやっていて、この処理をしたことが無い人はいないのでは？
ってくらいよくある処理ですよね。
単純に書こうとすれば、初心者でもすぐ書けるような方法がいくらでもあるので、
普段はあまり考えない部分かもしれません。
ですがこの処理の書き方はいろいろなやり方があり、
コードの読みやすさの、1つのポイントになる部分です。
読みやすさ、メンテのしやすさ、バグの書きづらさなど、
追求しようと思えば、とても考えがいのある処理です。
自分の知識の確認の意味でも、ちょっと考察してみましょう。
- Range("セルアドレス")で指定する
- Range("○○")を変数で指定できるようにする
- Range(Cells(左上), Cells(右下))で指定する
- Resize(データ数)で取得する
- Offsetで取得する
- 表全体の範囲から、列部分を切り取って取得する
- 表全体を簡単に取得するプロパティ
- おまけ：学んだコードは関数化してとっておく
- おまけ：1つ忘れていたメソッドを付け足し
Range("セルアドレス")で指定する
Range("E3:E7")
まずどストレートにこれ。基本中の基本ですね。
「初心者向け」と見せかけて、単純な読みやすさでは一番なので、
「完全に固定の範囲」であれば、上級者になっても普通に使うと思います。
Range(○○)はいろんな書き方ができますね。
やる人はいないと思いますが、
Range("E3,E4,E5,E6,E7")
でもいけます。
意外と知らない方もいるかもしれませんが、
Range("E3","E7")
でも同じエリアを取得できますし、
Range("E3:E4","E6:E7")
と、範囲から範囲まででも行けたりします。
まあこの辺は遊びというか、実際にこれらの指定方法で何かマクロを組んだことはほとんどないですね(笑)
一応たまに使うものとしては、「A1セルからこの表までの全体」を取得したいときに、
Range(セル範囲, セル範囲)パターンが使えます。
Range("A1", "A2:E7")
これでA1:E7を取得することができ、
ここを印刷範囲にしたり、.Rows.Countで最終行を取得したりできますね。
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を取得することができます。
セル範囲を数値で表そうと思ったら、これが一番基本だと思います。
もちろんこんな数字ベタ打ちでは何の意味もなく、
「目的は変数・定数を使えるように」です。
変数と定数を使った例がこんな感じです↓
' モジュール上部にて 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」が、たくさんのマクロに登場するでしょう。
全部"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くだもの表.売上))
_ が . に変わっただけですね。
入力は、
と、本体の名前「CNoくだもの表」の後は選択肢から選べるので、サクサク入力できます。
列挙型Enumは、単にLong型の定数をまとめて持っておく機能ですが、
「番号を省略した場合は連番」という仕様が、列番号の定数化に超便利です。
先ほど例に挙げた「品物と単価の間に購入日の列を挿入」する場合、
Enum CNoくだもの表 No = 1 品物 購入日 ' これを書き加えると、 単価 ' ここから↓が1ずつ足される神仕様 数量 売上 ' さっきの例の通り、5が6になっている End Enum
これで済むという、ある意味列番号を定義するために生まれてきたやつです。
Enumを初めて知った方は、この機会に覚えてしまいましょう。
Resize(データ数)で取得する
さて、定数化がようやく終わりましたので、
やっとこさ本題のRange取得プロパティを紹介できますね。
ここからはサクサク行きましょう。
まずはResizeプロパティから。
Resize(行数,列数)でセル範囲を拡張できます。
それを活用して、E3:E7を取得するにはこう書きます↓
' データの数を計算しておく Dim データ数 As Long データ数 = RLastくだもの表 - R1stくだもの表 + 1 ' E3:E7はこれ Range("E3").Resize(データ数, 1) ' 列数は変えないので省略も可能 Range("E3").Resize(データ数) ' 定数を使うならこう Cells(R1stくだもの表, CNoくだもの表.売上).Resize(データ数)
「一番目のセルからデータの数だけ下へ伸ばした範囲」という書き方が、
直感的にとてもわかりやすいため、素晴らしく読みやすいコードになります。
しかも一度取得した「データ数」は使いまわせるため、
みかんの売上合計 = WorksheetFunction.Sumif _ (Range("B3").Resize(データ数), "みかん", Range("E3").Resize(データ数))
のように、SUMIF(B列,みかん,E列)がこんなに読みやすく書けます。
Resizeを使いこなせると、Rangeの扱いにかなり幅が出ますので、
是非習得していってください。
例えば今回の表全体であるA3:E8なんかも、
Range("A3").Resize(データ数, 表の列数)
で取得することができます。
Offsetで取得する
Resizeの次は、親戚のOffsetを紹介します。
Rangeオブジェクト.Offset(行数, 列数)で、
Rangeオブジェクトを「同じ大きさのまま指定の数分ズラす」ことができます。
' D列の表エリアを右にひとつズラしてE3:E8を取得する Range("D3:D7").Offset(0, 1)
この例のように、大きさが変わらないというのがOffsetのいいところで、
例えばさっきのSUMIFを例にとると、
Set 売上列 = Range("E3").Resize(データ数) みかんの合計 = WorksheetFunction.Sumif(売上列.Offset(0, -3), "みかん", 売上列)
こんな書き方をすることができます。
読みやすくはないですが、書くのはかなり速そうですね。
定数が使えていないことからもわかる通り、
Offsetは書きやすいけど読みにくいプロパティです。
Resizeと組み合わせると、変幻自在に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列から始まっていると、
かなり読みやすくていい方法なんですが、
この表のように、B列以降から表が始まる場合は注意が必要です。
この場合、
Range("B3:E7").Columns("E")
これはシート上で言うところのF列を指してしまいます。
B列からみたE列のようなイメージになっちゃうため、
A列始まりでない表では、その分ズレちゃうんですね。
ということで、Columnsの性質を覚えつつ、本命は↓へ。
Intersectメソッドで取得する
Intersectメソッドは、引数に渡すセル範囲が「重なっている部分」を返します。
今回の場合は、「表全体の長方形」と「E列全体」の重なる部分を取ると、
画像のように売上列のブロックを取得することができます。
こんな感じで範囲を取得します。「交差部分」と呼んだりしますね。
これなら表エリアの開始列がA列かどうかに関わらず、
「第5列」なら5で行けますので、定数との相性も良いです↓
Intersect(Range("B3:E7"), Columns(CNoくだもの表.売上))
私はEnumを愛しているので、Enumと相性のよいこれが一番好きです。
表全体を簡単に取得するプロパティ
さて、わざわざこれらの「のこぎり」を便利と紹介したのは、
元の板となるRange("A2:E7")を、簡単に取得するいいプロパティがあるからです。
まずは以下の2つを覚えておきましょう。
CurrentRegionプロパティ
あるセルを含む、表っぽい部分を自動取得してくれます。
この機能は「アクティブセル領域」と呼び、
「Ctrl + Shift + :」 にショートカットがセットされていますので、
試しにやってみてください。
空の行だったり、表が複数隣接していない限りは、
手っ取り早く表のセル範囲を取得できる優秀なプロパティです。
今回の例では、
Range("A2").CurrentRegion
でRange("A2:E7")を取得できます。
というのは嘘で、Range("A1:E7")を取得しやがります。
大変便利なCurrentRegionさんですが、
今回のように、「表のタイトル」などをよくアホの子しますので、
ご利用は計画的に。
ワークシート.AutoFilter.Range
ワークシート.AutoFilter.Rangeで、オートフィルターのかかっているエリア全体を取得できます。
親のオブジェクトが「ワークシート」なので、
どこか起点となるセルを探さなくてもいいのがメリット。
Worksheets("くだもの売上表").AutoFilter.Range
これで"A2:E7"を取得できます。
セル番地が一つも出て来ないコードにできますね。
ユーザー側で設置しちゃえるので、Excelがアホの子しないのもメリットです。
（ユーザーがアホの子する危険がありますが）
今回のように、キレイにフィルターがかかっているなら、こっちを使いましょう。
見出しを除外する方法
さて、さっきからA2:E7を取得して喜んでいますが、
みなさん薄々感じています通り、本当に欲しいのは実はA3:E7です。
ということで、「見出しの1行を範囲から外す」方法が必要です。
が、残念ながらこの「範囲を縮小」、
一発で済むプロパティが用意されていません。
なので、今までの方法を組み合わせて、こうやります↓
Set データエリア = ActiveSheet.AutoFilter.Range Set データエリア = データエリア.Offset(1).Resize(データエリア.Rows.Count - 1)
理屈は簡単。
エリアをOffset(1)で1つ下にズラして見出しを消した後、
おしりがはみ出た分をResize(データの行数-1)で1行減らして完了です。
知っていれば簡単に済みますが、思いつくのは大変ですね。
このOffset + Resize によるエリアの縮小も覚えてしまいましょう。
ちなみに、一番下に空白の行が入っても処理に影響がない場合は、
「データエリア.Offset(1)」だけでもOKです。
例えば売上の合計をSUMで合計する場合は、
Resize(行数-1)をやらなくても、同じ値になりますからね。
完成形
これでやっと完成ですね。
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。
例えば、
この表で「みかんの売上の合計」を取得したい場合は、
WorksheetFunction.Sumif _ (GetRangeオートフィルターの列ブロック(ws集計シート, CNoくだもの表.品物), "みかん" _ , GetRangeオートフィルターの列ブロック(ws集計シート, CNoくだもの表.売上)
これで済みます。
読みやすいし、今日習ったプロパティ一個も書かなくてもいいし、
関数(プロシージャ)分割の練習にもなるし、言うことなしですね。
関数を作るのは最初は難しいかもしれませんが、
簡単な汎用関数で慣れておくと、複雑な処理を関数でパーツ分けするときの予習になります。
是非トライしてみてください。
ということで、ここまで読んでくださったお礼に、この関数をプレゼントします。
もうこのページに来ていただけないのは寂しいですが、
あなたの幸せなVBAライフを、草葉の陰から見守っていますよ(´∀｀)
おまけ：1つ忘れていたメソッドを付け足し
この長ーい記事を校正していたら、有名なやつを1つ忘れてたのを思い出しました。
E3:E7は↓これでも取得できます。
Union(Range("E3:E4"), Range("E5:E7"))
範囲同士をくっつける、Unionメソッドです。
今回の例では全く役に立ちませんが、
便利な時もたまにあるので、頭の片隅にでも置いておいてください。
やっとこさ書き上げて、いまさらどこに追記すりゃええねん。
と思ったのですが、
このおまけに付け足した感じがまさしくUnionです。
覚えやすそうで気に入ったので、このままここに書いておきますね(´∀｀)
