【EXCEL VBA | ListObject】ワークシートでもVBAでもテーブルは便利(^o^) その２

ワークシートでも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