セル範囲の取得

  1. Home
  2. Excel
  3. VBA
  4. セルの操作

操作の対象セル範囲が不定の場合

ここまでの解説では、操作の対象を「セルA1に」とか「セル範囲A1:C3に」などと簡単に決めてきました。もちろん解説なのですから、それは勘弁していただきたいのですけど、実際のマクロ作成では操作の対象を特定することに苦労するものです。操作の対象セルを、どう的確に特定するかが、VBAのビギナーとベテランで差が出るところでもあります。たとえば、次のようなデータを考えてみましょう。

このデータをマクロでクリアします。ただし、データが何行何列あるかは、そのときによって異なります。セルをクリアするにはRangeオブジェクトのClearContentsメソッドを使います。次のコードはセル範囲A1:D5をクリアします。

Sub Sample1()
    Range("A1:D5").ClearContents
End Sub

クリアすべきセル範囲の左上はセルA1です。したがって、セルD5の位置を取得できれば、上のコードと同じようにクリアできそうです。Excelを知らない方なら、次のように考えるかもしれません。

A列を下方向に調べてデータが入力されている最終行を調べ、同じように1行目を右に見ていきデータの終わりを調べ、それぞれの数値からアドレスを作って・・・もちろん、その方法でも可能です。ですが、けっこう大変ですよ。その方法でやってみましょうか。

Sub Sample2()
    Dim r As Long, c As Long, i As Long, LastCellAddress As String
    For i = 2 To 100
        If Cells(i, 1) = "" Then
            r = i
            Exit For
        End If
    Next i
    For i = 2 To 100
        If Cells(1, i) = "" Then
            c = i
            Exit For
        End If
    Next i
    LastCellAddress = Chr(64 + c) & r
    Range("A1:" & LastCellAddress & "").ClearContents
End Sub

試してみましたが、一応クリアはされるようです。しかし、For Nextステートメントで空欄セルに行き当たったら、Exit Forでループを抜けるテクニックや、文字コードから文字列へ変換するChr関数、文字列と""(ダブルコーテーション)を結合する方法など、ここには多くのテクニックが含まれています。今まで6000人以上のユーザーにExcelを教えてきた経験によると、これらのテクニックは、いずれもビギナーにとってイメージしにくく難しい技術です。

まずExcelの機能を覚えよう

困りました。困ったときはマクロ記録です！ですが、記録する操作に注意しなければなりません。たとえば、セル範囲A1:D5を選択して[Delete]キーを押す操作を記録してもダメです。

Sub Macro1()
    Range("A1:D5").Select
    Selection.ClearContents
End Sub

上のようなコードが記録されてしまいます。これでは役に立ちません。今必要なのは、セル範囲A1:D5をクリアするコードではなく、セルA1から始まって、データが入力されている全てのセルをクリアする操作です。こんなときは次のように操作します。

  1. アクティブセルをセルA1に移動します
  2. [Ctrl]キーと[Shift]キーを押しながら[End]キーを押します
  3. セル範囲A1:D5が選択されますので[Delete]キーを押してクリアします

この操作を記録したコードです。

Sub Macro2()
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.ClearContents
End Sub

Excelでは、[Shift]キーを押しながらアクティブセルを移動すると、移動した範囲が選択状態になります。さらに、Excelでは、[Ctrl]キーを押しながら[End]キーを押すと、そのシートで使用されている範囲の右下にアクティブセルが移動します。したがって、アクティブセルを現在使用しているセルの左上(セルA1)に置いた状態で、[Ctrl]＋[Shift]＋[End]キーを押すと、現在使用しているセル範囲が自動的に選択されるのです。この操作をマクロ記録します。何度も書きますが、VBAをマスターしようと思ったら、まずExcelの機能に精通するべきです。Excelの機能を理解しないでマクロなんか作れません。難解なVBAを学習するよりも、Excelのショートカットキーを覚える方がはるかに簡単です。また、同様の理由から、Excelに精通していない人からVBAを習ってはいけません。

閑話休題。さて、削除したいセル範囲を自動的に取得するには、もうひとつ方法があります。Excelでは、[Ctrl]＋[Shift]＋[*]キーを押すと、アクティブセルを含むデータ範囲を自動的に選択できます。データ範囲とは、空白セルに挟まれたデータの固まりです。アクティブセルをセルA1に移動して、この操作をマクロ記録してみましょう。

Sub Macro3()
    Selection.CurrentRegion.Select
    Selection.ClearContents
End Sub

SpecialCellsやCurrentRegionはヘルプで調べてください。それぞれ、どんな動作をするか分かった上でヘルプを読むのですから、理解するのも難しくないでしょう。もうひとつ、UsedRangeで取得する方法もありますが、それは下記で解説します。

セル範囲を自動取得する

上で解説したように、あるセル範囲(データが入力されているかたまり)を取得するには、次のような方法があります。

  1. 任意のセルが含まれているひとかたまりのセル範囲

    任意のセル.CurrentRegion

    「ひとかたまりのセル範囲」とは、空白行と空白列で区切られたセル範囲のことです。上図では「Range("B2").CurrentRegion」で「セル範囲A1:D5」が返り、「Range("C8").CurrentRegion」なら「セル範囲C8:F12」が返ります。これは、ワークシート上で[Ctrl]＋[Shift]＋[*]キーを押したのと同じ動作です。

  2. 任意のセルから 最終セルまでの セル範囲

    Range(任意のセル, ActiveCell.SpecialCells(xlLastCell))

    これは、ちょっと難しいです。まず

    　Range(始点セル, 終点セル)

    という指定で「始点セル から 終点セル までのセル範囲」を取得できます。この使い方については、下記で解説します。今回の例では

    　始点セル → 任意のセル

    　終点セル → ActiveCell.SpecialCells(xlLastCell)

    です。次に、SpecialCellsというのは、[F5]キーを押して[ジャンプ]ダイアログボックスを開き、[セル選択]ボタンをクリックして表示される[選択オプション]ダイアログボックスでセルを選択する操作を実現する命令です。

    引数に指定した「xlLastCell」は[最後のセル]オプションに相当します。"最後のセル"は、ワークシートで使われている"右下セル"を表しますので、SpecialCellsの前に指定するセルはどこでもいいです。なので、上記の例では「ActiveCell.SpecialCells」としています。

    上図では、ワークシートで使われている最後のセル(右下セル)は、セルF12です。したがって、

    　始点セル → 任意のセル

    　終点セル → セルF12

    となります。まぁ、こうした使い方をするケースでは、一般的に、始点セルにはセルA1を指定することが多いです。もちろん、別のセルを始点セルに指定してもかまいません。

  3. ワークシートで使用されているセル範囲

    ワークシート.UsedRange

    UsedRangeは、任意のワークシートで使用されているセル範囲を返します。使用されているセル範囲とは、セルA1から"最後のセル"までです。何も入力されていないワークシートでは、セルA1を返します。UsedRangeは、ワークシートが持つ命令です。使うときは

    　ActiveSheet.UsedRange

    とか

    　Sheets("Sheet1").UsedRange

    のように指定しなければなりません。

1行分のセル範囲を取得する

セルを特定するには

　Range(セルのアドレス)

　Cells(行番号, 列番号)

のように指定します。複数のセル範囲を特定するには、Rangeを使って

　Range("A5:D5")

のように書きます。

このように、マクロを作成する時点で、操作するセル範囲が確定しているときは、アドレスを直接記述すればいいですが、マクロを実行して、マクロ中で確定したセルを操作するようなケースでは、どうしたらいいでしょう。

たとえば、A列から"土屋"を探すなら、次のようにします。

Sub Sample1()
    Dim i As Long
    For i = 2 To 5
        If Cells(i, 1) = "土屋" Then
            Cells(i, 1).Select
            Exit For
        End If
    Next i
End Sub

このようにして、単一のセルを見つけて、その後そのセルを操作するのは簡単です。ところが実務では、見つかったセルを含む行全体を操作したいことが多いです。つまり、見つかったセルA5からセル範囲A5:D5を自動的に導きたいわけです。

こんなときに便利なのがRange(Cells, Cells)という書き方です。これは、上でも触れましたが

　Range(始点セル, 終点セル)

のように使います。

始点セルとは、あるセル範囲で左上セルのことで、終点セルは右下セルを表します。つまり、Rnage(Range("A1"), Range("D5"))は、セル範囲A1:D5となります。

ですが、こんな無駄なことを好む人はいないでしょう。マクロを作る時点で「始点セルがA1、終点セルがD5」と分かっているのなら、素直にRange("A1:D5")と書けばいいんです。何も悩む必要はありません。そうではなく、マクロ中で見つかった始点セルと、その始点セルと同じ行にある別の列(ここではD列)で構成されるセル範囲を取得したいんです。先の

Sub Sample1()
    Dim i As Long
    For i = 2 To 5
        If Cells(i, 1) = "土屋" Then
            Cells(i, 1).Select
            Exit For
        End If
    Next i
End Sub

で、"土屋"と入力されているセルが見つかりました。これが始点セルです。

　始点セル → Cells(i, 1)

終点セルは、同じ行(i行目)のD列ですから

　終点セル → Cells(i, 4)

となりますね。したがって、"土屋"が見つかった行のA列からD列を選択するには、次のようにします。

Sub Sample1()
    Dim i As Long
    For i = 2 To 5
        If Cells(i, 1) = "土屋" Then
            Range(Cells(i, 1), Cells(i, 4)).Select
            Exit For
        End If
    Next i
End Sub

Selectの前に、括弧を2つ「))」入力するのを忘れないでください。忘れないでくださいね、と何度注意しても、セミナーでは必ず数人が忘れます。ちなみに、私もときどき忘れます(^^;

対象のセル範囲を特定できれば、あとは自由自在です。そのセル範囲の文字色や背景色を設定したり

Sub Sample1()
    Dim i As Long
    For i = 2 To 5
        If Cells(i, 1) = "土屋" Then
            Range(Cells(i, 1), Cells(i, 4)).Font.ColorIndex = 3
            Exit For
        End If
    Next i
End Sub

見つかったセル範囲を別のシートにコピーしたり。

Sub Sample1()
    Dim i As Long
    For i = 2 To 5
        If Cells(i, 1) = "土屋" Then
            Range(Cells(i, 1), Cells(i, 4)).Copy Sheets("Sheet2").Range("A1")
            Exit For
        End If
    Next i
End Sub

ある特定の条件に一致するデータ(行)だけ、別のシートに集約するなんてのも、この考え方を使えば簡単にできますね。ぜひ覚えておきたいテクニックです。もちろん、ほかの方法でも実現できます。たとえばResizeを使えば、次のように書けます。

Sub Sample1()
    Dim i As Long
    For i = 2 To 5
        If Cells(i, 1) = "土屋" Then
            Cells(i, 1).Resize(1, 4).Select
            Exit For
        End If
    Next i
End Sub

Resizeは、セル範囲の大きさを広げたり縮めたりする命令です。もちろん、この方法でもできますし、Resizeを使えると別のケースでも役立ちます。しかし、Resizeはビギナーがイメージしにくい命令のひとつです。慣れないうちは無理をせず、まずRange(Cells, Cells)をマスターしてください。

次のように力わざで実現することもできますが

Sub Sample1()
    Dim i As Long
    For i = 2 To 5
        If Cells(i, 1) = "土屋" Then
            Range("A" & i & ":D" & i).Select
            Exit For
        End If
    Next i
End Sub

美しくありませんね。もし、リストの構造が変わって、D列ではなくE列までを対象とするようなとき、コード中の":D"をすべて修正しなければなりません。大きなマクロでは細心の注意が必要です。まぁ、石橋を叩かないで渡るような、怖い者知らずの方は一括置換でギャンブルするでしょうけど、臆病者の私には、とてもそんな度胸はありません。Range(Cells, Cells)やResizeなら、拡張する列の数値を定数などで定義できますから、一箇所直せば完了です。

Sub Sample1()
    Dim i As Long
    Const LastColumn As Long = 4    ''拡張する列の位置
    For i = 2 To 5
        If Cells(i, 1) = "土屋" Then
            Range(Cells(i, 1), Cells(i, LastColumn)).Select
            Exit For
        End If
    Next i
End Sub