野口香社会保険労務士事務所

群馬県にある社会保険労務士事務所のページです。

  • 【ExcelVBA】範囲指定の仕方【初心者向】

【ExcelVBA】範囲指定の仕方【初心者向】

本日は、ExcelVBAの範囲指定の仕方について説明します。初心者向けです。

目次

環境

こういうワークシートがあったとします。

今回使う環境

これだけ見ても分からないと思うので、ちょっと操作します。

まずはF5キーを押します。

F5キーを押したら開く「ジャンプ」のウィンドウ

何やら見慣れないウィンドウボックスが開きました。

これはジャンプと言います。

ジャンプが何をするものなのか、知らなくていいです。

知らなくても、使っているうちにそのうちに分かるようになります。

何も考えず「セル選択」というボタンを押してください。

すると次のような画面が開きます。

セル選択ボタンを押すと開く「選択オプション」画面

この画面も何なのか、今は分からないままでいいです。

何も考えず「数式」と言うところをチェックして「OK」ボタンを押してください。

数式を選択した状態

そうすると今度はワークシート上の数式がすべて選択された状態になりました。

グレーセルになっているところには数式が入っています。

数式の中身を確認するには、そのセルをアクティブにして(選択して)数式バーを見ます。

数式バーと言うのは、fxというマークがあるところです。

数式バー

VLOOKUP関数が入っているのが分かりますでしょうか。

セルB1からD12までにはVLOOKUP関数が入っています。

VLOOKUP関数が参照しているのは、セルK2からL30までの範囲です。

セルK2からL30までの範囲には表があります。

その表をVLOOKUP関数で参照しているのが列BからDまでです。

今回こういう環境でVBAで範囲指定していきます。

ボタン

リボンの開発タブをクリックし、デザインモードにしてください(開発タブがリボンにないときは、こういうサイトを参考に、開発タブを表示するようにしてください)。

「クリア」と書いてあるボタンを選択すると、数式バーに何やら見慣れない文字が書いてあると思います。

コマンドボタンを選択したところ

これがActiveXのボタンです。

「ActiveXって何？」と思ったそこのあなた。気にしなくていいです。ActiveXというのは最初のExcelにはなかった機能です。あとからできました。今はそれだけの理解でいいです。

さて、ここからが本題です。

このクリアボタン、範囲を選択してセルの値をクリアする機能を持っています。

どのようにクリアするか？

コードを見てみましょう。

Alt+F11を押してください。(Altキーを押しながら、F11キーを押す)

VBE

Alt+F11を押すと、VBEの画面が開きます。

VBE

VBEというのは、Microsoft Visual Basic for Applications Editorの略です。VBAエディタってことです。

「なんじゃそれ？」と思ったそこのあなた。それでいいです。VBEが何なのか、今は分からなくていいです。使っているうちに分かってきますので、今は分からないまま飛ばしてください。それで大丈夫です。

で、コードを見てみます。

Option Explicit

Private Sub CommandButton1_Click()
Union(Range("A1"), Range("E1:G1")).ClearContents
Union(Range("A2"), Range("E2:G2")).ClearContents
Union(Range("A3"), Range("E3:G3")).ClearContents
Union(Range("A4"), Range("E4:G4")).ClearContents
Union(Range("A5"), Range("E5:G5")).ClearContents
Union(Range("A6"), Range("E6:G6")).ClearContents
Union(Range("A7"), Range("E7:G7")).ClearContents
Union(Range("A8"), Range("E8:G8")).ClearContents
Union(Range("A9"), Range("E9:G9")).ClearContents
Union(Range("A10"), Range("E10:G10")).ClearContents
Union(Range("A11"), Range("E11:G11")).ClearContents
Union(Range("A12"), Range("E12:G12")).ClearContents
Union(Range("A13"), Range("E13:G13")).ClearContents
Union(Range("A14"), Range("E14:G14")).ClearContents
Union(Range("A15"), Range("E15:G15")).ClearContents
Union(Range("A16"), Range("E16:G16")).ClearContents
Union(Range("A17"), Range("E17:G17")).ClearContents
Union(Range("A18"), Range("E18:G18")).ClearContents
Union(Range("A19"), Range("E19:G19")).ClearContents
Union(Range("A20"), Range("E20:G20")).ClearContents
End Sub

何やらUnionというものと、Rangeというものと、ClearContentsというものが書かれているのが分かりますか？

一行目のOption Explicitというのは無視してください。

あなたの環境では表示されていないかもしれません。環境によって表示されたりされなかったりします。そういうものです。Option Explicitが何なのか、今は知らなくていいです。

次のPrivate Sub CommandButton1_Click()というのは、ワークシートにあったクリアボタンをクリックしたときのプログラムですよと言う意味です。要は、プログラムの名前です。

CommandButton1_Clickが名前です。()は名前に付属するオプションです。Subは名前の属性です。PrivateはSubの属性です。今は意味が分からなくてよいです。なんとなく、「ふーーーん、あっそ」くらいで流してもらっていいです。

ちょっと先走ったことを言いますので、大丈夫でしょうか、ついてきてくださいね、最後の行にEnd Subっていうのがあります。

これはプログラムの最後の意味です。

Subから始まったプログラムは、End Subで終わります。

SubからEnd Subまでの間に書かれたコードがプログラム本体ということです。

コード

次に、コードの意味を説明します。

Union(Range(“A1”), Range(“E1:G1”)).ClearContents

これはセル範囲A1とE1:G1の内容だけをクリアしますという意味です。

Unionというのは組み合わせるとか組合っていう意味です。

セルA1とE1:G1

なんでセルB1からD1までは飛ばしてあるのか？そう疑問に思ったそこのあなた、ありがとうございます、そう思ってくださることこそがねらいです。

セルB1からD1までは数式が入っています。

セルB1からD1までは数式が入っている

数式をクリアしたらまずいんですね。

だからセルA１から跳んで、E1:G1と範囲指定している訳です。

ところで、この範囲指定、いろいろな指定の仕方があるってご存じでしたか？

いろいろな範囲指定

これも同じ範囲指定の仕方です。

Range("A1,E1:G1").ClearContents

さっきと何が違うか分かりますか？

そう！

Unionがなくなりました。それから、”(ダブルクォーテーション)の位置とか数が違います。でも同じ意味です。

このように、ExcelVBAでは同じ意味でもいろいろな書き方ができます。

マクロの記録を使ってVBAを書いている人は、こういう記述の仕方の方がなじみがあるかもしれませんね。マクロの記録はいろいろ無駄なコードが多かったり、逆に必要な部分が省略されていたりするので、使うときは要注意です。

ところでこれも↓同じ範囲指定の仕方なんですが、さっきと何が違うか分かりますでしょうか？

Union(Cells(1, 1), Range("E1:G1")).ClearContents

一気に難易度が高くなった？ごめんなさい、でもこの書き方の方が応用が利くので、ぜひ覚えてもらいたい書き方なんです。

かっこの数が多くなったので見づらくなっていますが、

Union(Range(“A1”), Range(“E1:G1”)).ClearContents

のRange(“A1”)の部分がCells(1,1)に変わりました。

ワークシートって言うのは普段こういう↓のが見慣れていると思うのですが、

普段見慣れているワークシートの列表示

こういう↓風に、列番号を数字にできるっていうのはご存じでしたか？

列表示が数字

見え方が変わっただけで、本質部分(Excelの内部での処理)は変わりません。

この表示の仕方で、Range(“A1, E1:G1”).ClearContentsとしても、Union(Cells(1, 1), Range(“E1:G1”)).ClearContentsとしても、動作に変わりはありません。

いずれもセル範囲A1とE1からG1までをクリアします。

Cellsプロパティでは、引数を2つもちます。引数には順番によって決まりがあります。

最初の引数が行番号です。2番目の引数が列番号です。

Cells(1, 1)とあったら、1行目1列目のセルという意味です。つまりセルA1です。

では、さらに難易度が高くなります。

Union(Cells(1, 1), Range(Cells(1, 5), Cells(1, 7))).ClearContents

Cells(1, 5)とあったら、1行目5列目のセルという意味です。つまりセルE1です。

Cells(1, 7)とあったら、1行目7列目のセルという意味です。つまりセルG1です。

この二つのセルを、RangeでつなぐとRange(“E1:G1”)と同じ意味になります。

分からなかったら20行書いてよい

さて、ここまでいろいろなセル範囲の指定の仕方を見てきました。

元のコードに戻ってみてみましょう。

Option Explicit

Private Sub CommandButton1_Click()
Union(Range("A1"), Range("E1:G1")).ClearContents
Union(Range("A2"), Range("E2:G2")).ClearContents
Union(Range("A3"), Range("E3:G3")).ClearContents
Union(Range("A4"), Range("E4:G4")).ClearContents
Union(Range("A5"), Range("E5:G5")).ClearContents
Union(Range("A6"), Range("E6:G6")).ClearContents
Union(Range("A7"), Range("E7:G7")).ClearContents
Union(Range("A8"), Range("E8:G8")).ClearContents
Union(Range("A9"), Range("E9:G9")).ClearContents
Union(Range("A10"), Range("E10:G10")).ClearContents
Union(Range("A11"), Range("E11:G11")).ClearContents
Union(Range("A12"), Range("E12:G12")).ClearContents
Union(Range("A13"), Range("E13:G13")).ClearContents
Union(Range("A14"), Range("E14:G14")).ClearContents
Union(Range("A15"), Range("E15:G15")).ClearContents
Union(Range("A16"), Range("E16:G16")).ClearContents
Union(Range("A17"), Range("E17:G17")).ClearContents
Union(Range("A18"), Range("E18:G18")).ClearContents
Union(Range("A19"), Range("E19:G19")).ClearContents
Union(Range("A20"), Range("E20:G20")).ClearContents
End Sub

このコードは1行目をクリアコンテンツした後、似たような動作をさらに19回繰り返しています。

ワークシートの1行目から20行目までを順番にクリアコンテンツしています。

これはこれで「あり」です。

分からなかったら、こういう風に自分にとって確実な方法で書いてしまえばよいと私は思っています。

VBAっていうのは、人が便利に使えばよいと思います。

便利に使えてこその道具です。

だからたとえ冗長であっても、作った人が分かっていることが大事です。

使いこなしてなんぼの世界。分からないまま使ってもだめです。

でもこのコードの書き方だと困ることが一つだけあります。

何かというと、仕様変更があってコードを修正とか追加したりするときです。

例えば今は20行だけですけど、これが100行とか、1000行とかになったら？

コードを修正するのは大変ですよね。

では、どうするか？

私なら、こんな風にします。

修正後のコード

修正後のコードはこんな感じです。

Option Explicit

Private Sub CommandButton1_Click()
Union(Range(Cells(1, 1), Cells(20, 1)), Range(Cells(1, 5), Cells(20, 7))).ClearContents
End Sub

最初に20行あったコードが1行だけになってしまいました。

しかし、やっていることはいっしょです。

Unionというのは、引数にセル範囲を複数指定できます。

セル範囲の指定の仕方は、Cellsを使ってもいいし、Rangeを使ってもいいです。

今回はRangeだけを使いました。

Rangeの中でCellsを使った範囲指定をしています。

最初のRangeではセルA1からA20までを指定しています。次のRangeではセルE1からG20までを指定しています。

ワークシートではこんな感じ↓の範囲指定をしているということです。

飛び石でセル範囲を選択したところ

このコードの書き方だと何がよいかっていうと、今は20と書いてある箇所を修正するだけで、100行でも1000行でもすぐに対応できるっていうことです。

「ちょっと待って！こういう書き方でもいいんじゃない！！？」と疑問に思ったあなた。

Option Explicit

Private Sub CommandButton1_Click()
Union(Range("A1:A20"), Range("E1:G20")).ClearContents
End Sub

はい、OKです。こちら↑でも同じことです。

それから、これ↓もOKです。

Option Explicit

Private Sub CommandButton1_Click()
Range("A1:A20, E1:G20").ClearContents
End Sub

自分にとって分かりやすいコードで書いてください。

きっかけ

今回こういう記事を書こうと思ったのは、Yahoo!知恵袋で次のような質問に遭遇したからです。

https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q10274432590

VBAについて教えてください。
A1に行番を打ち込むことでB1からD1にVLOOKUPでデータを読み込んで表示、E1からG1は手打ちで情報を入力する表を使っています
この形態の表が20行あります。
ActiveXのボタンでこの行を部分的にクリア(A1とE1からG1)するものを20行、記述を手打ちで範囲を入力しているのですがこれを効率よく作成する方法はあるのでしょうか？
今の20行からもう20行増やそうと思うのですが早く作る方法があれば教えてください！お願いします。

補足
数式を避けて消去する、もしくは数式を埋め込む？というのが出来ればいいなと漠然的に考えています

https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q10274432590

私がこの質問に遭遇した時点で質問してからすでに6日経過していましたが、だれも回答していませんでした。

具体的なコードが何も書いていないことと、ワークシートの画像もないことから、質問者さんがどれくらいのスキルレベルで、どんな悩みをもっているか分からないからだと思いました。

そこで私なりにいろいろ推測しつつ、こんなスキルレベルの方かなあと想像して答えてみましたが、果たしてこれで分かったでしょうか・・・？

補足にある、数式を避けて消去するやり方は、

Range(Cells(1, 1), Cells(20, 7)).SpecialCells( _
        xlCellTypeConstants, xlNumbers + xlTextValues + xlLogical + xlErrors) _
        .ClearContents

と書きます。xlNumbers + xlTextValues + xlLogical + xlErrorsの部分は23でもいいです。同じことです。

選択オプションの定数

もう少し説明すると、定数(xlCellTypeConstants)には種類があって、数値(xlNumbers,1)、文字(xlTextValues,2)、論理値(xlLogical,4)、エラー値(xlErrors,16)の4つです。すべてを指定して定数を選択したいときは、23を指定します(1+2+4+16)。