Excel VBAエキスパート体験記22(Table操作2)

前回はExcel VBAエキスパートスタンダードのTable内の追加と削除について勉強しました。
前回までの記事はこちら

前回はExcel VBAエキスパートスタンダードのTable内の行と列の取得について勉強しました。前回までの記事はこちら今回は7章のTableの操作の続きについ…

今回は7章のTableの操作の続きについてレポートしていきます。今回で4回目です。

なお理解が間違っているところもあるかもしれませんがわからない人と同じ視点に立てるのは今しかないので細かいところはご容赦ください。
間違えに気づいたら修正していきます（笑）。

＜今回の目的＞

Table内の特定のデータの操作について理解する

今回の勉強方法

8章を精読する
Table内の特定のデータの操作をわかりやすく説明する

結論
今回躓いた部分
AutoFilterを用いた特定データの削除
AutoFilterを用いた特定データのフォント変更
AutoFilterを用いた特定データのコピー
ここでのまとめ

結論

いきなりですが結論です。

テーブル内のデータを探すのにはAutoFilterメソッドを使う
フィルター後の行削除ではDataBodyRangeやRangeは使えない

次から詳しく説明します。

今回躓いた部分

前回Deleteメソッドは各々のオブジェクト（DataBodyRange,Range,ListColumns(), ListRows())に対して使うと学びました。
ただAutoFilterを使用した後の行の削除ではDataBodyRange.EntireRow.DeleteとEntireRowを追加して説明されていました。なぜそのままは使えないかを理解するのに時間がかかりました。

テキストの説明が少し曖昧でしたので今回はそこを中心に説明したいと思います。

AutoFilterを用いた特定データの削除

データを検索する方法は6章で学びましたがテーブル内のデータを検索するのはAutoFilterメソッドが便利です。
以前のAutoFilterメソッドの記事についてはこちら

テーブル内での検索はDataBodyRangeかRangeオブジェクトに対してAutoFilterメソッドを使用します。

Range(“A1″).ListObject.Range.AutoFilter 列数,”抽出文字列”

以下は以下のテーブルの1列目に豊臣が含まれる文字列を検索し、その列を削除するコードです。

しかしこれはうまくいきません。

Sub Sample9()

'テーブル内のフィルター行の削除 これはできない
  Range("A1").ListObject.DataBodyRange.AutoFilter 1, "*豊臣*"
  Range("A1").ListObject.DataBodyRange.Delete
  Range("A1").ListObject.DataBodyRange.AutoFilter

End Sub

このコードでは削除の際に以下の警告が出てマクロが止まります。

これが出る理由は「AutoFilter実行後は行削除はシート行全体にのみしかできない」仕様によるものです。

実際AutoFilterをかけた後に特定の範囲だけを削除しようとすると以下のようになります。

行だけはシート行全体となっていることが分かります。

フィルターをかけるということはある条件に従って行全体を並び替えるということです。その後一部分だけ削除するとその行関係がずれてしまいます。

その為DataBodyRangeやRangeなど行全体ではなく一部だけを変えようとする操作はできなくなっているのです。

正しくはDataBodyRangeやRangeで取得したオブジェクトに対しEntireRowを使用します。

Sub Sample10()

'テーブル内のフィルター行の削除
  Range("A1").ListObject.DataBodyRange.AutoFilter 1, "*豊臣*"
  Range("A1").ListObject.DataBodyRange.EntireRow.Delete
  Range("A1").ListObject.DataBodyRange.AutoFilter

End Sub

EntireRowを使うことで行全体を示すようになり警告が出ずにうまく処理することができます。

AutoFilterを用いた特定データのフォント変更

特定データに関係する部分のみフォントを変更したい場合などのコードが以下です。

Sub Sample11()

'テーブル内のフィルター行のフォントを変更
  Range("A1").ListObject.DataBodyRange.AutoFilter 1, "*豊臣*"
  Range("A1").ListObject.DataBodyRange.Font.Bold = True
  Range("A1").ListObject.DataBodyRange.AutoFilter

'テーブル内のフィルター列のフォントを変更
  Range("A1").ListObject.DataBodyRange.AutoFilter 1, "*豊臣*"
  Range("A1").ListObject.ListColumns(3).DataBodyRange.Font.Bold = True
  Range("A1").ListObject.DataBodyRange.AutoFilter
  
End Sub

最初のテーブルは以下のようになります。2つ目のコードはC4のみを変更させているので変化はありません。

AutoFilterを用いた特定データのコピー

テーブル内のフィルター行をSheet2のA1セルにコピーするコードは以下です。
なおRangeでコピーする際はアクティブセルがテーブル内にないと非表示行も含めてコピーする不具合があるそうなのでDataBodyRangeを使うほうが安心です。

Sub Sample12()

'テーブル内のフィルター行をコピー（見出し行を含まない）
  Range("A1").ListObject.DataBodyRange.AutoFilter 1, "*豊臣*"
  Range("A1").ListObject.DataBodyRange.Copy Worksheets("Sheet2").Range("A1")
  Range("A1").ListObject.DataBodyRange.AutoFilter
  
'テーブル内のフィルター行をコピー（見出し行を含む）(これはテーブル内にアクティブセルがないとうまくいかない）
  Range("A1").ListObject.Range.AutoFilter 1, "*豊臣*"
  Range("A1").ListObject.Range.Copy Worksheets("Sheet2").Range("A1")
  Range("A1").ListObject.DataBodyRange.AutoFilter
  
End Sub