セル範囲をテーブルにする

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

まずは、セル範囲をテーブルにしてみましょう。ちなみにこの操作は、ほとんど必要ないはずです。なぜなら、テーブルは通常クラウドのサーバーなどから、Excelにエクスポートされる形で提供されることが多いからです。もちろん、CSVを読み込んだり、従来のワークシートからデータを集約させて、従来のセル範囲をテーブルに変換したい場合もあるでしょう。

ここでは、下図のような表で解説します。表は[Sheet1]に作成していて、特に明記しない限り[Sheet1]がアクティブシートとします。

アクティブセルを表内に置いて、手動でこのセル範囲をテーブルにしてみます。その操作をマクロ記録すると、おおむね次のようなコードが記録されます。

Sub Macro1()
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$D$10"), , xlYes).Name = "テーブル1"
End Sub

ListObjectオブジェクトはテーブルを表します。アクティブシートに新しいテーブル(ListObjectオブジェクト)を追加(Add)しろという命令です。このAddメソッドには、次の引数が用意されています。

  • SourceType
  • Source
  • LinkSource
  • XlListObjectHasHeaders
  • Destination
  • TableStyleName

マクロ記録で記録されたのは、このうち引数SourceType、引数Source、引数XlListObjectHasHeadersの3つです。

もちろん、この記録されたコードをそのまま使ってもいいのですが、検討すべきは引数XlListObjectHasHeadersに指定した「xlYes」と、作成したテーブルの名前を「Name = "テーブル1"」のように定義しているところです。引数XlListObjectHasHeadersは、表の先頭行をタイトル(見出し)にするかどうかを指定しています。今回先頭行はタイトルですから「xlYes」です。まぁ、普通の表は先頭にタイトルがありますので、ほとんどの場合は「xlYes」でいいでしょうが、ここは元の表によって変わるところです。ちなみに、テーブルはデータベース領域ですから、必ずタイトルが必要です。もし、タイトルの存在しないデータだけの表からテーブルを作成しようとすると(つまり、引数XlListObjectHasHeadersにxlNoを指定すると)下図のように、便宜的なタイトルが自動的に設定されます。

なお、引数XlListObjectHasHeadersを省略すると、Excelがタイトル行かどうかを自動的に判定してくれます。もし、手動でテーブルに変換するとき、Excelが先頭行をタイトル行だと自動判定してくれるような表でしたら、引数XlListObjectHasHeadersは省略してかまいません。自動的に先頭行がタイトルになります。

さて、マクロ記録では、テーブルを作成すると同時に"テーブル1"という名前を設定しています。テーブルには必ず、ユニーク(重複しない)名前が必要です。ワークシートみたいなものですね。ワークシートにもユニークな名前が必要でしょ。ここでは"テーブル1"を設定しています。手動でテーブルを作成するときは、現在使われていない新しい名前を、Excelが自動的に設定してくれます。しかし、これを毎回自分で調べて設定するのは現実的ではありません。実は、新しく作成するテーブルの名前(Nameプロパティ)を設定しないと、Excelが便宜的に付けてくれます。新しく作るテーブルに何か特別な名前を付けたいのでしたら別ですが、どんな名前でもいいのでしたら、設定しない＝Excelに付けてもらう と考えるのが簡単です。名前を設定しない場合は、Addメソッドの括弧をつけません。

Sub Macro1()
    ActiveSheet.ListObjects.Add xlSrcRange, Range("A1:D10")
End Sub

ついでに、無駄な絶対参照の$を消しました。これで十分です。なお、なぜ括弧をつけないのか。どんなとき括弧をつけるのかは、下記ページをご覧ください。

括弧はどんなときに使うの？

ああ、ついでに。「Range("A1:D10"), , xlYes」のようにカンマ(,)が2つ続いていた理由は、下記ページをご覧ください。

引数名って書かなくていいの？

だいぶ実用的になってきました。マクロ記録では、テーブルに変換するセル範囲を「Range("A1:D10")」と固定していますが、実際にはデータの量が分かりません。次のようにするといいでしょう。

Sub Macro1()
    ActiveSheet.ListObjects.Add xlSrcRange, Range("A1").CurrentRegion
End Sub

縞々模様がウザイ

テーブル機能が今ひとつ人気ないのは、標準で設定される縞々模様ではないでしょうか(私はそう思ってますw)。また、テーブルには標準で縞々模様が設定されるため、中には「テーブルって、表を1行おきに色を塗る機能でしょ」とか「テーブルって、表を綺麗に装飾する機能でしょ」など、とんでもない誤解している人もいます。違うんですよ。さて、手動操作でテーブルに変換するとき、リボンからテーブルの装飾を選択します。

どれかの装飾を選ばなければならないので「テーブルには必ず装飾が設定される」と誤解していませんか？そして、その装飾がウザイ、いらないと感じている人はいませんか？(私はそう感じていますw)実は、テーブルを作った後であれば、何の装飾もない、スッピンのテーブルにすることができるんです。作成したテーブル内にアクティブセルを移動すると、リボンに[テーブルツール]-[デザイン]タブが表示されます。この中に、ちゃんと「なし(無色)」ってのがあるんです。

だったら最初から無色のテーブルを作れよと感じてるのは私だけでしょうか。別にいいですけど。

さて、この無色のテーブルをいきなり作成することはできないのでしょうか。テーブルを追加(作成)するAddメソッドには、引数TableStyleNameがあります。これが怪しいです。ここに"無色"を指定すれば、いいのではと。結果的には、あれこれ試しましたが無理っぽいです。このAddメソッドは引数を見る限り、ワークシートのセル範囲をテーブルにするだけでなく、サーバーのデータからテーブルを作成することもできるようです。引数TableStyleNameは、おそらくそういうときのために用意されている引数で、ワークシートのセル範囲からテーブルを作成するときには効果がないようです。

だったらしかたありません。一度作ったテーブルを"無色"にします。すでに作成されたテーブル(ListObjectオブジェクト)の装飾を"無色"設定するには、ListObjectオブジェクトのTableStyleプロパティに空欄("")を指定します。

Sub Macro1()
    ActiveSheet.ListObjects.Add xlSrcRange, Range("A1").CurrentRegion
    Range("A1").ListObject.TableStyle = ""
End Sub

これで"無色"のテーブルを作成できます。最後の「Range("A1").ListObject」に関しては、次の「テーブルを特定する3つの方法」で解説します。

テーブルを普通のセル範囲に変換する

では、テーブルを普通のセル範囲に変換するには、どうしたらいいでしょう。分からなかったらマクロ記録です。次のようなコードが記録されます。

Sub Macro2()
'
' Macro2 Macro
'

'
End Sub

何にも記録されません・・・なんじゃ、そりゃって感じです。バグというよりも「テーブルにしてね！普通のセル範囲には戻さないでね！」というMicrosoftの"意思"を感じるのは考えすぎでしょうか。ヘルプを調べると、Unlistメソッドで普通のセル範囲に戻せると書いてありました。

Sub Macro3()
    Range("A1").ListObject.Unlist
End Sub

※2023年11月 追記※
本稿執筆時点では、上記のようにマクロ記録されませんでしたが、つい先日やってみたら記録されるようになっていました。どのバージョンからマクロ記録されるようになったかは確認していません。マクロ記録されないのはMicrosoftの"意思"云々というのは、もちろん冗談ですよ。ミスか不具合でしょうから、どこかのタイミングで修正したのでしょうね。

ただし、セル範囲に変換するだけでは、自動的に設定されたウザイ装飾がそのままです。元のセル範囲に戻したのですから、装飾も消してもらいたいです。しかたありません、自動的に消えないのでしたら自分で消すまでです。先のコードで、スッピンの"無色"テーブルに設定してから、セル範囲に変換してやります。

Sub Macro3()
    Range("A1").ListObject.TableStyle = ""
    Range("A1").ListObject.Unlist
End Sub

あるいは

Sub Macro3()
    With Range("A1").ListObject
        .TableStyle = ""
        .Unlist
    End With
End Sub