VBA(Excel)

Excel VBAでセル範囲を指定する方法

スポンサーリンク

こんにちは。システム担当の父親、KATSUです。
Excel歴20年、Access歴10年ですが、ブログは65記事を書いた程度です。


VBAをこれから学びたい人には、おそらくこのようなことが頭の中に浮かんでくるのではないでしょうか?

「VBAを使ってExcelでの作業を自動化したいけど、一体何を自動化すれば良いのか?」

この問いを更に掘り下げてみました。

Excelで文章を書いたりグラフを作ったりすることもあるかもしれませんが、最も行われているのは表作りです

思い通りの表を作るためには、「セル範囲を指定できること」が必要です。

そこで今回は、VBAを使って表を作るときの基本となる、セル範囲の指定について書いてみました。

この記事を書いている私は、約10年前からExcelVBAを使っています。

使い始めの時は、マクロ記録のみに頼った範囲指定を行っていました。

マクロ記録だけでは思い通りの表を作れず、運用し始めては何度も作り直す作業を繰り返してきました。

しかし、正しいセル範囲の指定方法を学び、実務で活用することにより手直し回数が激減しました。

今回紹介する方法をマスター出来れば、項目数を増やした場合にも、行数や列数を自動で調整できるようになります。

また、今回はセル選択の応用にも触れてみました。

Accessなど他のアプリからExcelのセルを取得する方法です。

VBAでExcelの表作りを自動化できるようになりたい方に、少しでも今回の内容が役に立てれば幸いです。

マクロ記録でもセル範囲を指定できるが…

マクロ記録でA1セルからD10セルまでを選択した場合、

Range(“A1:D10”).select

と記録されます。

「“A1:D10”」は文字列です。

マクロ記録と同じ方法でセル範囲を指定し直すためには、かっこの中の文字列を変えなければいけません。

作りたい表の行数や列数はいつも同じではありません。

Rangeの中を「”A1:D10”」といった文字列で指定するのは不十分なので、少し手を加える必要があります。

基本はCellsと変数を組み合わせてセル範囲を指定

まずは、Rangeの中に開始セルと終了セルを入れてあげるとよいです。

Range(Cells(1,1),Cells(10,4)).Select

上のように書き換えても、マクロ記録と同じ動作になります。

Cellsの第一引数は行で、第二引数は列です。

更に、変数を一緒に活用すると、変化に強いプログラムコードになります。

Cellsを使ったサンプルコードを実際に実行してみましょう。

Sub Get_Range1()
    Dim i As Integer '変数iを指定
    For i = 0 To 9 '0から9まで繰り返し
        Range(Cells(i + 1, 1), Cells(i + 1, i + 1)).Select
        Selection.Interior.ColorIndex = 3
    Next
End Sub

サンプルコードはそのまま実行すると一瞬で終わってしまいますので、必ずステップインで動きを確認しましょう。

また、最近このようなツイートをしたところ、20人近くの方が共感してくれています。

OffsetやResizeも活用して自由自在にセル範囲を指定

Rangeを使わずにセル範囲を選択できるようになると、様々な場面で活用できます。

それは、CellsにOffsetやResizeを組み合わせる方法です。

Offsetは原点から何マスずらすか、Resizeは何マス分範囲を広げるか指定します。

実際にサンプルコードを実行してみましょう。

Sub Get_Range2()
    Dim i As Integer, j As Integer '変数iとjを指定
    For i = 0 To 19 'iを繰り返し
        For j = 0 To 9 'jを繰り返し
            Cells(1, 1).Offset(i, j).Value = i * 10 + j
        Next j
    Next i
End Sub

行数と列数分だけ繰り返すので、二重ループにしています。

実行してみると、このように値が貼り付けられます。

OffsetやResizeを組み合わせてデータを貼り付ける方法は、データベースからワークシートに欲しいデータのみを選んで反映させる場合に重宝します。

VBAで行や列を指定する

Excelを操作する時に、行や列を選択することがありますよね?
マクロ記録の場合はセル範囲と同じように文字列で記録されますが、数値に書き直した方が制御しやすいプログラムになります。

1行や1列を選択する

1行や1列をマクロ記録すると、

Columns("B").Select
Rows("3").Select

と記録されます。

ColumnsやRowsを数値で指定する場合は、

Columns(2).Select
Rows(3).Select

となります。

複数行や複数列を選択する

複数行や複数列をマクロ記録した場合は、下のようになります。

Columns("A:E").Select
Rows("1:4").Select

文字列を数値に指定しなおすこともできますが、ここで注意しないといけないことがあります。

Columns(1:5).Select
Columns(1,5).Select

上の場合はエラーになってしまいます。

このようにRangeを組み合わせて使えば、エラーを起こしません。

Range(Columns(1), Columns(5)).Select
Range (Rows(1), Rows(4)).Select

他のアプリからExcelのセル範囲を指定する

AccessやVB.NETなどの別のアプリからExcelを操作することも出来ますが、マクロ記録のコピペのみでは正しく動かすことができません。

以下の2つのことに注意する必要があります。

シートから指定し直す

先ほど行や列を指定する方法について指定しましたが、他アプリから操作できるようにするには、下のようにシートから指定しなおさないといけません。

Sheets(”Sheet1”).Range(Columns(1), Columns(5)).Select
Sheets(”Sheet1”).Range (Rows(1), Rows(4)).Select

ここでは、"Sheet1"と文字列指定していますが、ワークシートの場合は好みでも大丈夫です。

シートの枚数が増減する場合は数値(シート番号)で指定する必要がありますが、枚数を増やしすぎると使いづらくなります。

少ない枚数でシート名で指定してあげるくらいが使いやすいです。

設定「xl~」を数値に修正する

例えばマクロ記録で表の最終行を取得した場合、

Selection.End(xlDown).Select

と記録されます。

他のアプリで同じコードでは正しく動きません。
そんな時にはヘルプを活用します。

まずは表示タブからオブジェクトブラウザーを表示させましょう。

RangeクラスのEndメンバーを選択します。

そのまま右クリックし、ヘルプをクリックします。

マイクロソフトの公式リファレンスが開き、対象のページが表示されます。

表示されているページ内の、データ型「XlDirection」をクリックします。

移動方向についての値が表示されます。

ここでは、xlDownは、「-4121」となっています。

Selection.End(-4121).Select

と修正して実行してみると、マクロ記録と同じ動作になります。

まとめ

今回は、Excel VBAでセルを操作する方法について紹介しました。
Excel VBAを使いこなすには、セル範囲を思い通りに操作できなければなりません。
基本はマクロ記録されたものをそのまま使うと、冗長なメンテナンスしづらいコードになってしまいます。

  • 文字列を数値に書き換える
  • 条件分岐・繰り返し・変数をフル活用する

上のことを行うと、少ない行数で複雑なセルの操作を行うことが出来ます。

また、Excel VBAのスキルが伸びてくると、将来は他のアプリからExcelを操作したくなります。

そんな時は、今からヘルプに慣れておけば大きく役に立ちます


本日もありがとうございました。

スポンサーリンク

-VBA(Excel)