ワークシートでもVBAでもテーブルは便利(^o^)
前回次の記事で、VBAでテーブルを扱うときに使用するListObjectで、テーブルやいろいろな部位を捕まえる方法を紹介しました。
今回は、その捕まえたテーブルのデータを操作する方法を紹介しいます(^o^)
サンプルデータとして、以下のテーブルがあることを前提とします。
オートフィルター
まずは、オートフィルターです。
テーブルの実データを指定して、そこに対して”.AutoFilter”を実行します。
Sub sample()
'シートの指定
Dim シート As Worksheet
Set シート = ActiveSheet
'テーブルの指定
Dim テーブル As ListObject
Set テーブル = シート.ListObjects("テーブル1")
'テーブルの実データの指定
Dim テーブルのデータ As Range
Set テーブルのデータ = テーブル.Range
'オートフィルターの設定
テーブルのデータ.AutoFilter 3, "男"
End Subこのコードを実行すると、テーブルにフィルターが掛けられて絞り込まれることが分かります。
'テーブルの実データの指定
Dim テーブルのデータ As Range
Set テーブルのデータ = テーブル.Range実データの指定には、見出し含むテーブルデータ全体を指定する”.Range”を使いましたが”.DataBodyRange”でも結果は同じになります。
'テーブルの実データの指定
Dim テーブルのデータ As Range
Set テーブルのデータ = テーブル.DataBodyRange ’ここ変えても同じコピーと貼り付け
フィルターで絞り込んだ状態のテーブルのデータをコピーして、別のところに貼り付けるには、フィルターで絞り込んだ後に".Range" あるいは ".DataBodyRange" に対して、Copyメソッドを実行します。
.Rangeに対して行うと、見出しを含むデータがコピーされます。.DataBodyRangeに対して実行すると、見出しを含まないデータだけがコピーされます。
Sub sample()
'シートの指定
Dim シート As Worksheet
Set シート = ActiveSheet
'テーブルの指定
Dim テーブル As ListObject
Set テーブル = シート.ListObjects("テーブル1")
'テーブルの実データの指定
Dim テーブルのデータ As Range
Set テーブルのデータ = テーブル.DataBodyRange
'オートフィルターの設定
テーブルのデータ.AutoFilter 3, "男"
'フィルターを掛けたデータをコピペ（見出し含むデータ）
テーブル.Range.Copy シート.Range("A19")
End Sub
見出し含まないデータだけをコピペする場合”.DataBodyRange”
Sub sample()
'シートの指定
Dim シート As Worksheet
Set シート = ActiveSheet
'テーブルの指定
Dim テーブル As ListObject
Set テーブル = シート.ListObjects("テーブル1")
'テーブルの実データの指定
Dim テーブルのデータ As Range
Set テーブルのデータ = テーブル.DataBodyRange
'オートフィルターの設定
テーブルのデータ.AutoFilter 3, "男"
'フィルターを掛けたデータをコピペ（見出し含まないデータ）
テーブル.DataBodyRange.Copy シート.Range("A19")
End Sub
ここではテーブルの指定方法は、ListObjectを使用しましたが、もちろん構造化参照でも同じことが出来ます。
※注意
このプログラム実行時、アクティブセルがテーブルの外側にあると絞り込まれた状態ではなく、テーブルの全てのデータがコピペされてしまいます。不具合みたいですが注意してください。
失敗バージョンも動画でどうぞ。コードは同じです。
特定の列だけ取り出すことも出来ます。
その場合、最後のコピーメソッドを".ListColumns("列名")"などテーブルの指定の際に、列だけにしておきましょう。
Sub sample()
'シートの指定
Dim シート As Worksheet
Set シート = ActiveSheet
'テーブルの指定
Dim テーブル As ListObject
Set テーブル = シート.ListObjects("テーブル1")
'テーブルの実データの指定
Dim テーブルのデータ As Range
Set テーブルのデータ = テーブル.DataBodyRange
'オートフィルターの設定
テーブルのデータ.AutoFilter 3, "男"
'フィルターを掛けたデータをコピペ（見出し含む名前列）
テーブル.ListColumns("名前").Range.Copy シート.Range("A19")
End Sub
もちろん、
テーブル.ListColumns("名前").Range.Copy シート.Range("A19")
を
テーブル.ListColumns("名前").DataBodyRange.Copy シート.Range("A19")
にすれば、見出し無しのデータになります。
そして、
”テーブル.ListColumns("名前").DataBodyRange”に対しては書式の設定などもすることが出来ますね。
行の削除
行を削除する場合は、".DataBodyRange"の".EntireRow"を".Delete"すると実現出来ます。
全ての行を削除する
見出しだけ残して、全ての行を削除したい場合は次のようになります。
Sub sample()
'シートの指定
Dim シート As Worksheet
Set シート = ActiveSheet
'テーブルの指定
Dim テーブル As ListObject
Set テーブル = シート.ListObjects("テーブル1")
'テーブルの実データの指定
Dim テーブルのデータ As Range
Set テーブルのデータ = テーブル.DataBodyRange
'見出し以外の全ての行を削除する
テーブルのデータ.EntireRow.Delete
End Sub特定の行だけ削除する
特定の行だけ削除したい場合は、先にフィルターで絞っておきましょう。
Sub sample()
'シートの指定
Dim シート As Worksheet
Set シート = ActiveSheet
'テーブルの指定
Dim テーブル As ListObject
Set テーブル = シート.ListObjects("テーブル1")
'テーブルの実データの指定
Dim テーブルのデータ As Range
Set テーブルのデータ = テーブル.DataBodyRange
'性別が”男”のデータを削除
With テーブルのデータ
.AutoFilter 3, "男"
.EntireRow.Delete
.AutoFilter 3
End With
End Sub
列の挿入
最後に列の挿入です。
列を挿入するには、ListColumnsコレクションのAddメソッドを使用します。
ListColumnsコレクションを指定して、”.Add”するだけです。
何もしないとテーブルの一番右側に挿入されます。
引数で、挿入位置を指定することも出来ます。
Sub sample()
'シートの指定
Dim シート As Worksheet
Set シート = ActiveSheet
'テーブルの指定
Dim テーブル As ListObject
Set テーブル = シート.ListObjects("テーブル1")
'列の指定（コレクション）
Dim 列 As ListColumns 'コレクションなので複数系
Set 列 = テーブル.ListColumns
'列を挿入(一番右)
列.Add
'列を挿入（左から3番目）
列.Add 3
End Sub
