テーブルを操作するのVBA
VBAでテーブルのオブジェクトを操作するにはWorksheetオブジェクトメンバのListobjectオブジェクトにアクセスすることになります。
下記によく使うメソッドとプロパティを例文を含めて紹介していきます。コード集です。この記事は解説を入れてリライトする予定です。
テーブル化する
ActiveSheet.ListObjects.Add xlSrcRange, Selection.CurrentRegion見出し行[ヘッダー行]を取得する
Dim HeaderRowNum As Long Dim HeaderRow As Range HeaderRowNum = ActiveSheet.ListObjects[1].HeaderRowRange.Row '行番号 Set HeaderRow = ActiveSheet.ListObjects[1].HeaderRowRange 'Rangeオブジェクト列数を取得する
Dim ColsCnt As Long ColsCnt = ActiveSheet.ListObjects[1].Listcolumns.Count行数(レコード数)を取得する
ActiveSheet.ListObjects[1].Listrows.Countテーブルのデータ範囲を全選択する(ヘッダーを含めない)
ActiveSheet.ListObjects[1].DataBodyRange.Selectテーブルのデータ範囲を全選択する(ヘッダーを含める)
ActiveSheet.ListObjects[1].Range.Select1件目のレコードの1列目のアドレスを取得する
'1列目 ActiveSheet.ListObjects[1].ListRows[1].Range[1].Address '3列目 ActiveSheet.ListObjects[1].ListRows[1].Range[3].Address Dim MyAddress as String MyAddress = ActiveSheet.ListObjects[1].DataBodyRange[1,1].Address Dim MyRow , MyCol as Long Dim MyAddress as String MyRow = ActiveSheet.ListObjects[1].DataBodyRange.Row MyCol = ActiveSheet.ListObjects[1].DataBodyRange.Column MyAddress = ActiveSheet.Cells[MyRow , MyCol].Addressレコード(行)の選択
'3行目を選択する。 ActiveSheet.ListObjects[1].ListRows[1].Select最終行番号を取得する(テーブルとして定義している領域の最終行)
下記のように求めます。
見出し行番号(テーブルの開始行番号)+ レコード数(テーブルの行数)
最終行番号を取得する(実際にデータが入っている最終行)
ListRows.Countではテーブルとして定義されている領域の最終行が取得される。
つまり、実際には3行目までしかデータが入っていなくても、10行目までテーブルが定義してあると、RowsCnt=10となってしまうのです。RowsCnt=3を取得したい場合はこちらです。
最終列番号を取得する
下記のように求めます。
1列目の列番号(テーブルの開始列番号) + カラム数(テーブルの列数) 1
シート内にテーブルが存在するかを判定する
上記のようにListobjectオブジェクトを使ってコードを書く場合は、テーブルが存在しない場合も考慮する必要があるかもしれません。シート内のテーブルの有無をチェックして、テーブルが存在しない場合にプログラムを終了する例を紹介します。
If ActiveSheet.ListObjects.Count = 0 Then Exit Subテーブルに名前を付ける
シート内で複数のテーブルを作成することはあまりお勧めできませんが、複数のテーブルを作成する場合は名前を付けると便利です。
テーブル範囲のリサイズ
カレントリージョンの場合
ActiveSheet.ListObjects[1].Resize Selection.CurrentRegionテーブルに行・列を追加
'行末に行の追加 ActiveSheet.range["A1"].ListObject.Listrows.add '2列目に列の追加 Positionオプションで場所指定 ActiveSheet.range["A1"].ListObject.ListColumns.add Position:=2列名の取得と列名の変更
with ActiveSheet.range["A3"].ListObject '最後列の列名を取得する msgbox .ListColumns[.ListColumns.count].name '最後列の列名を変更する .ListColumns[.ListColumns.count].name = "Product Name" End with集計行の表示
追加ではなく表示・非表示という表現をします。
ActiveSheet.ListObjects[1].ShowTotals = True集計行の取得
Dim Rng as Range Set Rng = ActiveSheet.ListObjects[1].TotalsRowRangeテーブルスタイルを変更する
'テーブルスタイル名は文字列で指定します。 ActiveSheet.ListObjects[1].TableStyle = "TableStyleLight3" 'テーブルスタイルを削除する(装飾をすべてなくす)には空の文字列を渡します。 ActiveSheet.ListObjects[1].TableStyle = ""テーブルを範囲に変換(テーブルを解除)
いきなり変換すると書式が残ってしまうので、テーブルスタイルを削除してから変換すべし。
行の挿入(レコードの追加)
'最終行に行挿入 ActiveSheet.ListObjects[1].ListRows.Add 'テーブルの3行目に行挿入 ActiveSheet.ListObjects[1].ListRows.Add[3]ちなみにAddメソッドにはAlwaysInsertというオプショナルな引数もありますが、私は使ったことがないのでここでの説明は省略します。
行・列単位のデータクリア
with ActiveSheet 3列目のデータをまるごと空にする .Cells[1, 1].ListObject.ListColumns[3].DataBodyRange.ClearContents end with行の全件削除(レコード全件削除)
下記のいづれか
ActiveSheet.ListObjects[1].DataBodyRange.Delete ActiveSheet.ListObjects[1].Range.Deleteテーブル範囲が列見出し行とデータボディレンジ1行の2行だけになります。(集計行を使っている場合は集計行も表示されます)中のデータは空っぽになります。
おわり
以上、テーブル関連(ListObject)のVBAのメソッドについてコード集でした。
ノンプログラマーでも自動化スキルを身につける。
- URL Copied!