前回テーブルに関する記事の初回で「Addメソッド」を使った簡易な1行のテーブル作成サンプルコードを紹介しましたが、改めてセル範囲をテーブルに変換する方法を詳しく解説していきます。あわせて、テーブルを解除して普通のセル範囲に戻す方法についても解説します。
「テーブル」に関することを順番に少しづつ勉強していきましょう(^^)/
今回はセル範囲をテーブルに変換する方法と解除する方法です。
ピボットテーブルで表示数値をダブルクリックしてみました！
ほんとに表示数値の元データがテーブルで表示されるんですね。今まで知りませんでした！ すごく「テーブル」に興味が出てきました。よろしくお願いします！
【この記事でわかることは】
・セル範囲をテーブルに変換する方法
・テーブルを解除してセル範囲に変換する方法
前回記事のおさらいは、下のカードをクリックすれば開きます(^^ゞ
前回記事は、「テーブル」に関する記事の初回です。導入部分ですので是非覗いてみてね(^^)/
セル範囲をテーブルにする方法
ワークシート上にある従来のセル範囲をテーブルに変換したい場合、スタイル等を都度設定するのは面倒なのでVBAを使って定型的に変換できるように設定しておけば便利です。
前回記事のサンプルコードでテーブルに変換してみます
'テーブル化するAddメソッドサンプル
Sub ListObjAddSample()
ActiveSheet.ListObjects.Add xlSrcRange, Selection.CurrentRegion
End Sub・デフォルトで作成されるテーブルがこれということですね。
・このコードでは、セル範囲をCurrentRegionを使って指定しています。
・ListObjectsはWorkSheetオブジェクトのプロパティで、ListObjectオブジェクトのコレクションです。ListObjectオブジェクトがテーブルを表していますので。その集合ということです。
・ListObjects.Addメソッドは、テーブルを追加するという命令です。今までテーブルが無かった場合テーブル名は「テーブル1」です。すでにテーブルがあった場合は、その数によりインデックス番号が付定されます。
ListObjects.Add メソッドを確認します
【構文】
ListObjects.Add (SourceType, Source, linksource, XlListObjectHasHeaders,
Destination, TableStyleName)
【パラメーター】
・SourceType （省略可能）元データの種類を指定します。
・Source （省略可能）元データのセル範囲を指定します。
・LinkSource （省略可能）外部データベースをリンクするかを論理値で指定します。
・XlListObjectHeaders （省略可能）元データの先頭行が見出し行かどうかを定数で指定します。
・Destination （省略可能）テーブルを作成する左上のセルをRangeオブジェクトで指定します。
・TableStyleName （省略可能）TableStyleの名前（例：”TableStyleLight1″）
【戻り値】
・新しいリストオブジェクトを表すListObject オブジェクト。
※これらの引数を使えばテーブルを詳細に設定する事は可能です。でも、定数はほぼ省略できるので特にこだわりが無ければ、sourceのみの設定で良いと思います。
・Microsoft Office サポートで検索すると、Excel のテーブルの概要には「関連するデータのグループを簡単に管理および分析するために、セルの範囲をExcelのテーブルに変換できます (以前は Excel リストと呼ばれていました)。」と書かれています。
・個人的には、データ表を「テーブル」というオブジェクト（Listobject）にして管理できるようにしたものと言えるのではないかと思っています。
セル範囲を指定できるテーブル化サンプルコード
・実行時にApplication.InputBoxを使用して指定できるように調整したコードです。
・選択範囲がすでにテーブルだった場合エラーになるのでその対策もしています。
'【汎用】選択範囲をテーブルに変換する
Sub ListObjAddSelection()
Dim ws As Worksheet
Dim rng As Range
Dim lobj As ListObject
'Application.InputBoxで列を指定できるようにする
On Error Resume Next
Set rng = Application.InputBox( _
Prompt:="対象セル範囲を選択指定するか" & vbCrLf & _
"先頭セルを選択してください！", Title:="対象セル選択", Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
rng.Worksheet.Activate
Set ws = ActiveSheet
'選択セルが一つだった場合はCurrentRegionする
If rng.Count = 1 Then
Set rng = rng.CurrentRegion
End If
On Error GoTo Err_Resume 'テーブルがすでにあった場合の対策
'セル範囲をテーブル化してListObject変数に代入
Set lobj = ws.ListObjects.Add(SourceType:=xlSrcRange, _
Source:=rng, _
XlListObjectHasHeaders:=xlYes)
'変数に入れておけば後で利用できる一例
MsgBox "セル範囲をテーブル名「" & lobj.Name & "」に変換しました！"
Exit Sub
Err_Resume:
Dim re As Long
re = MsgBox("すでにテーブル化されています。解除しますか？", vbYesNo, "確認")
If re = vbYes Then
'テーブル解除
ActiveSheet.ListObjects(1).Unlist
Resume 0
Else: MsgBox "処理を中止しました！"
End If
End Sub・17行目は、選択セルが単独だった場合、範囲をCurrentRegionで設定しています。
・19行目の「On Error GoTo Err_Resume」はテーブルがすでにあった場合のエラー対策
・21行目で、選択セル範囲をテーブル化して戻り値をListObjectの変数に代入しています。
コードは、Set lobj = ws.ListObjects.Add(Source:=rng) だけでもOKですね(^^)/
見出しなどはExcelが自動で判別設定してくれますから。
※ただし、すでに範囲がテーブルだった場合エラーが発生します。その場合27行目のエラー処理へジャンプします。
・25行目は、処理完了メッセージを出します。ここでListObject変数からテーブル名を取得する処理を入れています。
・27行目以降のエラー処理部分では、メッセージを出してテーブルを解除するかどうか確認します。「はい」選択でテーブルを解除して21行目に戻ります。「いいえ」で処理を中断します。
テーブルを解除してセル範囲に変換する
テーブルを普通のセル範囲に変換する方法も知っておいた方が良いでしょう。
テーブルに変換してみたけどやり直したい時などには、VBAならすばやく変換できます。
セル範囲に変換するサンプルコード
・リスト機能をListObjectオブジェクトから削除するUnlistメソッドが用意されていました。
・UnlistメソッドはListObjectオブジェクトのメソッドです。
・このメソッドを使用すると、テーブルの範囲を普通のセル範囲に戻せます。
'【汎用】選択テーブルを範囲に変換する
Sub DeList()
Dim ws As Worksheet
Dim rng As Range
Dim lobj As ListObject
'Application.InputBoxで列を指定できるようにする
On Error Resume Next
Set rng = Application.InputBox( _
Prompt:="対象テーブル内のセルを選択してください", _
Title:="対象テーブル選択", Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
rng.Worksheet.Activate
'セルがListObjectかを確認してから処理する
On Error Resume Next
If rng.ListObject.Name = "" Then
MsgBox "選択セルはテーブル（ListObject）ではありません。"
Else
Set ws = ActiveSheet
Set lobj = ws.ListObjects(rng.ListObject.Name)
lobj.TableStyle = "" 'スタイルを無地にする
lobj.Unlist 'テーブルを解除する
End If
On Error GoTo 0
End Sub・15行目の「On Error Resume Next」はテーブルが無いのに解除しようとするとエラーが発生するためです。17行目でエラーの場合メッセージを出し終了します。
・16行目で、テーブル名を取得する処理をしています。
・19行目からはテーブルを解除する処理部分です。
・20行目で、ListObject.Nameでテーブル名を指定してListObject変数に代入しています。
・21行目で、解除前にテーブルスタイルを「無地」に設定します。この部分は不要ならばコメントアウトしてください。
・22行目で、テーブルを解除してセル範囲に戻しています。
ブック内の全テーブルを一括解除する
・ブック内の全テーブルを一括で解除したい場合も想定されます。
・次のようなコードで一括解除が可能です。
'ブック内の全テーブルを解除するサンプル
Sub AllWsUnListObj()
Dim ws As Worksheet
'全ワークシートをループ
For Each ws In Worksheets
'各ワークシート内の全テーブルをループ
Dim lobj As ListObject
For Each lobj In ws.ListObjects
lobj.UnList 'テーブル解除
Next
Next
End Sub・ブック内の全ワークシートと各シート内の全テーブルをループして解除します。
・アクティブシートだけ対象にするなら、外側のループを外してActiveSheet.ListObjectsに変更するだけでOKです。
'シート内の全テーブルを解除するサンプル
Sub WsUnListObj()
'ワークシート内の全テーブルをループ
Dim lobj As ListObject
For Each lobj In ActiveSheet.ListObjects
lobj.UnList 'テーブル解除
Next
End Subまとめ（おわりに）
・いかがでしたでしょうか？
・「いつも汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーを念頭に記事を書いています。
・サンプルファイルを用意していますのでよろしければお使いください(^^)
まとめと感想など
「テーブル」関係の解説第2弾、セル範囲をテーブルに変換する。そしてその逆のテーブルを解除する方法の解説でした。
テーブルを解除する時のスタイルの部分はすごく不思議でした(^^;
ただ解除したらスタイルは解除されないし、「無地」にしたのに下から元の書式が現れるのが「気持ち悪い」と思いました！
色々試して見ないとわからない部分って結構あるんですよね！
でも、この部分はほんとに気持ち悪い部分ですね。
では、次回はスタイルの部分を検討してみましょうか(^^)
★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★
今後の記事について
今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてVBAを使う」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m
【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にもご期待ください(^^)/
記事のサンプルファイルをダウンロードできます
今回の記事のサンプルはこのリンク先からダウンロードできます！
過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら（このリンク先）からご利用ください
【今回分かったことは】
・セル範囲をテーブルに変換する方法
・テーブルを解除してセル範囲に変換する方法
・解除前にスタイルを「無地」に設定する方法
・エラー発生時の対応方法