新しいデータを追加する

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

テーブルに新しいデータを追加するには、どうしたらいいでしょう。まず、テーブルの行はListRowで表されます。ListRowの集合体がListRowsコレクションです。このListRowsコレクションのAddメソッドを使うと、テーブルの最下行に新しい行が追加されます。

Sub Sample1()
    Range("A1").ListObject.ListRows.Add
End Sub

新しい行を追加したことで、このテーブルには全部で「10個」の行(ListRow)が存在することになりました。いま新しく挿入した行が10番目です。この「10番目の行」は ListRows(10) で表されます。

Sub Sample2()
    Range("A1").ListObject.ListRows(10).Range.Select
End Sub

この「10」は「ListRowの個数」でしたね。ListRowの個数は、ListRows.Count で調べることができます。したがって、上記のコードは、次のように書けます。

Sub Sample3()
    Dim N As Long
    N = Range("A1").ListObject.ListRows.Count
    Range("A1").ListObject.ListRows(N).Range.Select
End Sub

以上を踏まえると、テーブルに新しい行を追加して、そこに新しいデータを代入するには、次のように考えられます。もう一度、データ件数9件の状態からやってみます。

Sub Sample4()
    Dim N As Long
    Range("A1").ListObject.ListRows.Add
    N = Range("A1").ListObject.ListRows.Count
    With Range("A1").ListObject.ListRows(N)
        .Range(1) = "2018/12/10"
        .Range(2) = "広瀬"
        .Range(3) = "C"
        .Range(4) = "1000"
    End With
End Sub

できました。できましたけど、これ面倒くさくないですか？まぁ、考え方としては間違っていないんですけど、テーブルに新しいデータを追加するのって、おそらく実務ではよくある操作だと思います。そのたびに、毎回こんなことをしないといけないのでしょうか。

手動操作で、テーブルに新しいデータを追加するときって、いちいちテーブルの範囲を広げてから入れませんよね。テーブルの真下セル(←ここはテーブルではない)に、いきなりデータを入れます。そうするとExcelが自動的にテーブルの範囲を拡張してくれます。手動操作でそうやるのですから、マクロでも同じに考えていいはずです。

問題はこのセル(今回はセルA11)を、どうやって自動的に取得するかです。今まででしたら、Endモードを使って、A列の一番下から上にジャンプして、行き着くであろうセルの1つ下という考え方で特定しました。

Sub Sample5()
    Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = "2018/12/10"
End Sub

しかし、これは古いというか、通常のワークシートでセルを特定する考え方です。ちなみに、書き方によっては、この方法だと失敗します。たとえば次のような感じです。

Sub Sample5()
    Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = "2018/12/10"
    Cells(Rows.Count, 2).End(xlUp).Offset(1, 0) = "広瀬"
    Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) = "C"
    Cells(Rows.Count, 4).End(xlUp).Offset(1, 0) = "1000"
End Sub

このマクロを実行すると、下図のようになります。

テーブルはデータベース領域なのですから、ここは発想を変えるべきです。EndモードやOffsetを使わないで、セルA11を特定してみます。

テーブルの1列目(左端の列)は、ListColumns(1) です。この列の全セルは、ListColumns(1).Range で表されます。ここのCountプロパティを調べれば、この列に何個のセルがあるか分かります。

Sub Sample6()
    MsgBox Range("A1").ListObject.ListColumns(1).Range.Count
End Sub

列の一番下セルということは、列内で10番目のセルということですから、次のように特定できます。

Sub Sample7()
    Dim N As Long
    N = Range("A1").ListObject.ListColumns(1).Range.Count
    Range("A1").ListObject.ListColumns(1).Range(N).Select
End Sub

今選択されているセルは、Range(10) です。で、ここからがちょっと難しいのですが。実はこの状態でも、Range(11)を操作できます。列内のセルは10個しかありません。でも、11個目のセルを指定できるんです。

Sub Sample8()
    Dim N As Long
    N = Range("A1").ListObject.ListColumns(1).Range.Count
    Range("A1").ListObject.ListColumns(1).Range(N + 1).Select
End Sub

セルが10個しかないのに11番目を操作できるなんて、ちょっと不思議に感じるでしょうけど、実はExcelでは昔からこういう操作ができました。たとえば、次のようなコードです。

Sub Sample9()
    Dim A As Range
    Set A = Range("A1:A3")
    A.Select
    A(4) = 100
End Sub

オブジェクト変数Aには、セル範囲A1:A3という「3個のセル」を代入しています。でも「A(4)」として、代入した範囲外も操作できます。1つ下だけじゃありません。次のようなコードも問題なく動きます。

Sub Sample10()
    Dim A As Range
    Set A = Range("A1")
    A(3) = "田中"
    A.Item(4) = "鈴木"
    A.Cells(4, 2) = "佐々木"
End Sub

「なぜ、こうなるのか」「なぜ、これができるのか」は、考えてはいけません。あなたのためです。こうした動作の仕組みを理解しようとするなら、そもそも「Rangeって何？」という迷宮に足を踏み込まなければなりません。これは、決して開けてはいけない扉です。ここは「なぜ、こうなるのか」「なぜ、これができるのか」と悩むところではありません。「なんか不思議～よく分かんないけど、できるんだ～へぇ～」と飲み込むところです。この仕組みを使うと、今回の課題である、テーブルの下のセルを簡単に特定できるんです。

話を戻しましょう。テーブルに新しいデータを追加するには、次のように考えます。

Sub Sample11()
    Dim N As Long
    With Range("A1").ListObject
        N = .ListColumns(1).Range.Count
        .ListColumns(1).Range(N + 1) = "2018/12/10"
        .ListColumns(2).Range(N + 1) = "広瀬"
        .ListColumns(3).Range(N + 1) = "C"
        .ListColumns(4).Range(N + 1) = "1000"
    End With
End Sub

実際には、このように1セルずつ代入することは希かもしれません。もし別の表からデータをコピーする場合でも、上でやったように「テーブルの左下のセル」を特定できれば、あとは何とかなるでしょう。ちょっと、やってみましょうか。

アクティブシートに下図のようなテーブルがあります。

同じブック内のSheet2に、下図のような普通の表があります。こっちはテーブルではありません。このSheet2の表から、[名前]列が"田中"のデータだけ、アクティブシートのテーブルに追加します。

Sub Sample12()
    Dim LC As ListColumn, N As Long
    Set LC = Range("A1").ListObject.ListColumns(1)
    N = LC.Range.Count
    With Sheets("Sheet2")
        .Range("A1").AutoFilter 2, "田中"
        .Range("A1").CurrentRegion.Offset(1, 0).Copy LC.Range(N + 1)
    End With
End Sub

じゃ、コピー元もテーブルだったら。

Sub Sample13()
    Dim LC As ListColumn, N As Long
    Set LC = Range("A1").ListObject.ListColumns(1)
    N = LC.Range.Count
    With Sheets("Sheet2").Range("A1").ListObject.DataBodyRange
        .AutoFilter 2, "田中"
        .Copy LC.Range(N + 1)
    End With
End Sub

こんな感じですかね。

テーブルのバグ

テーブルに新しいデータを追加するときに、注意しなければならないテーブルのバグがあります。もう、こちらは言い逃れができない、誰がどう見てもバグです。まずは、手動操作でやってみましょう。

下図のような簡単なテーブルを作りました。この後のことがあるので、装飾は「なし」にしています。

このテーブルに、手動で塗りつぶしの色を設定します。ここでは分かりやすくセルの塗りつぶしで解説しますが、ほかの書式でも同じ現象が起こります。

この状態で新しいデータを追加すると、テーブル内で設定した書式(塗りつぶし色)が引き継がれます。

これはいいです。正常です。では、テーブル内に設定されている書式を消します。塗りつぶし色が設定されているセルを選択して、リボンのボタンで[塗りつぶしなし]をクリックします。

もう新しいデータを入力しても、書式が引き継がれません。そりゃそうです。引き継ぐべき書式(塗りつぶし)は、もう設定していないのですから。

さあ、いよいよバグのご紹介です。同じ手順をもう一度繰り返します。まずテーブル内に書式(塗りつぶし)を設定します。

新しいデータに書式が引き継がれます。

テーブルに設定されている書式を消します。ただし、ここからの手順が違います。セルの書式をクリアするには、たとえば塗りつぶしなら、リボンの[塗りつぶしなし]ボタンや、[セルの書式設定]ダイアログボックスから消すことができます。あるいは、ほかにも手があります。何も書式が設定されてないブランクセルの書式をコピーする方法です。使用していないブランクセル(ここではセルD1)を選択して[書式のコピー/貼り付け]ボタンをクリックします。

マウスポインタの形がハケに代わります。ドラッグしてセルを選択すると、選択したセルにブランクセルの書式(つまり何もない)がコピーされます。

どちらも同じことですよね。少なくとも、見た目的には変わりません。でも、新しいデータを入力すると、

消したはずの書式が引き継がれてしまいます。これは、あり得ないでしょう。これ、マクロで代入しても、同じように消したはずの書式が引き継がれしまいます。

Sub Sample14()
    Dim N As Long
    With Range("A1").ListObject
        N = .ListColumns(1).Range.Count
        .ListColumns(1).Range(N + 1) = "小島"
    End With
End Sub

本件は、セミナーを受講された方から「なんかぁ～ときどきぃ～書式が設定されちゃうことがあるんですよぉ～」と相談されました。んなわきゃーないだろと思いましたが「じゃ、ちょっと調べてみますね」と。で、コメダ珈琲でコーヒー飲みながら確認したら、一発で再現できました。思わずコーヒーを吹き出しそうになりました。「書式のコピー/貼り付け」で起きるという発生要因を突き止められたのはラッキーです。私は、自作のアドインで「書式のコピー/貼り付け」にショートカットキーを割り当てています。いつものようにそれで消したら再現したわけです。調べたら、普通に[塗りつぶしなし]だと問題ないと。早々に修正していただきたいですね。