特定行のセルを編集する

  1. Home
  2. Excel
  3. VBA
  4. VBAでテーブルの操作

「特定の行だけ削除する」の考え方で、行全体に対して、削除だけでなくさまざまな編集を行えます。では、行全体ではなく、特定行の"セル"を部分的に編集するには、どうしたらいいでしょう。

ここでは、下図リストで[名前]が"田中"である[数値]だけ、文字色を赤に設定します。

まず[名前]列を"田中"で絞り込みます。

Sub Sample1()
    With Range("A1").ListObject
        .DataBodyRange.AutoFilter 2, "田中"
    End With
End Sub

絞り込まれている結果のうち、[数値]列はListColumns(4)です。今回は、タイトル行を除く実データだけ操作したいので、DataBodyRangeです。

ここに対して、文字色を設定します。

Sub Sample1()
    With Range("A1").ListObject
        .DataBodyRange.AutoFilter 2, "田中"
        .ListColumns(4).DataBodyRange.Font.ColorIndex = 3
        .DataBodyRange.AutoFilter 2
    End With
End Sub

行内のセルを1つずつ操作するのは超難しい

上記の考え方は、[名前]が"田中"であるという特定データの、[数値]列全部に対して同じ操作(文字色の変更)をしました。これなら簡単です。でも、[名前]が"田中"である行のセルを1つずつ操作しようとすると、難易度がメチャクチャ高くなります。たとえば、次のようなケースです。

[合計]列に、"田中"の行だけ数値1～数値3の合計を代入したいんです。"田中"ではない行には何もしません。つまりブランクセルのままです。いかがですか？実務では、ありがちな話ですよね。でもこれ、かなり難しくなります。難しい理由は、テーブルの正しい使い方ではないからです。テーブルはExcelが管理しているデータベース領域です。データベースであるなら、列にはすべて同じ意味合いのモノが入っていなければなりません。[名前]列にはすべて名前です。[日付]列にはすべて日付です。そして、もし何らかの計算をする列であれば、その列にはすべて同じ計算式あるいは同じ計算の結果が入っていなければいけないんです。"田中"の行(レコード)だけ計算をして、他の行(レコード)は計算をしない、という時点でもうデータベースではありません。だから難しくなるんです。道具の使い方が間違っています。テニスで、ラケットを逆に持って「難しい…」と嘆いているようなものですね。ちなに下の写真は私の手です。このために撮影しました。

まぁ、そんなことを言っても、上司に「やれ！」と言われたら「いや！」とは言えませんね。ただ、メチャクチャ難易度が上がることを覚悟してください。

"田中"で絞り込んだ、そのテーブル内で計算する方法をあれこれ考えましたが、なかなか簡単な手が思いつきません。データベースだったらどうするだろうと考えて、"田中"で絞り込んだ結果を、一度別のシートにコピーすることにしました。おそらく、それが一番簡単です。コピーする先は新規シートです。マクロ実行中に新規シートが表示されると格好悪いですから、非表示のシートとして追加します。"田中"で絞り込んだ結果を、その新規シートにコピーして合計を求めます。合計の計算にはSUM関数を使います。さあ、問題は計算結果を適切なセルに代入するところです。そこで、ちょっとだけ苦労しましたけど、For Eachで回してやると上手くいきました。次のような感じです。

Sub Sample2()
    Dim Target As ListObject, i As Long, C As Range
    Set Target = Range("A1").ListObject
    Target.DataBodyRange.AutoFilter 1, "田中"
    Sheets.Add.Visible = False
    Target.DataBodyRange.Copy ActiveSheet.Previous.Range("A1")
    With ActiveSheet.Previous
        For Each C In Target.ListColumns(5).DataBodyRange.SpecialCells(xlCellTypeVisible)
            i = i + 1
            C = WorksheetFunction.SUM(Range(.Cells(i, 2), .Cells(i, 4)))
        Next C
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
    Target.DataBodyRange.AutoFilter 1
End Sub

新しいシートを挿入したりしたら、マクロが遅くなるんじゃないかしら…と不要な心配をする方もいるでしょうから、所要時間も計測しておきました。上図のような7件で試したところ、私のPCで「0.047秒」でした。いやぁ～それはデータの件数が少ないからだろ～もっと多かったら、さぞかし時間がかかるんじゃ…と無駄な抵抗をする方もいるでしょうから、データの件数を増やして計測もしました。同じような形式の1,000件では「2.156秒」でした。だ、だけど…ウチのデータはもっと多いし！10,000件くらいになることもあるし！そうなったらきっと10分くらいかかるに決まってるし…と悪あがきをする方もいるでしょうから、10,000件でも計測しました。10分なんてとんでもない。「4.687秒」でした。まぁ、このくらいだったら許容範囲でしょう。

別の方法もあります。本来テーブルは、Excelがデータベースとして管理する特別な領域です。であれば、テーブル内のセルを1つずつ操作するという"ワークシート的"な発送は避けるべきです。ですが、今回のように特殊なケースでは、そうしたセオリーを無視して、あえて"ワークシート的"な操作でやる手もあります。繰り返しますが、これも相当難しいです。

Sub Sample3()
    Dim C As Range
    Range("A1").ListObject.Range.AutoFilter 1, "田中"
    With Range("A1").CurrentRegion.Offset(1, 0)
        For Each C In .Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Rows
            C.Range("E1") = WorksheetFunction.Sum(Range(C.Range("B1"), C.Range("D1")))
        Next C
    End With
    Range("A1").ListObject.Range.AutoFilter 1
End Sub

まぁ、ほかにもいろいろやり方はありますけど、いずれにしても、これはコードをコピペして何とかなるレベルではありません。上記のコードを読んで、意味が分からなかったらあきらめてください。あなたには、できません。