2022/10/15

ListObjectのデータ削除

ListObjectについて、下記のようなシリーズで説明しています。
　・ListObjectの作成と概要
　・ListObjectの並べ替え
　・ListObjectの絞り込み
　・ListObjectの絞り込み解除
　・ListObjectの絞り込みデータの配列化
　・ListObjectへのデータ追加
　・ListObjectのデータ変更
　・ListObjectのデータ削除　　←今回

今回は、ListObjectオブジェクトに対する「データ削除」について説明します。

１．テーブルに対するデータ削除処理（手動）

テーブルのある行（レコード）を手動で削除する際は、図１のように「削除する行をセル選択」し、マウス右クリック①で表示されるメニューから「削除」→「テーブルの行」を選択しクリック②することで削除できます。
テーブル上でデータ行を挿入
図１

「テーブルの行」を選択しての削除であれば、テーブルの１レコードだけが削除されるため、同じシートの同じ行にあるデータは削除されません（＝シートの行削除では無い）。但しこの「テーブルの行」削除が出来るのは、テーブルの絞り込みを行っていない時のみであり、絞り込みを行っている時には図３のように「シートの行全体を削除（ = EntireRow.Delete ）」になってしまいます。

また内容は図１と同じですが、削除したい行を選択③後マウス右クリックの代わりに、リボンの「ホーム」タブ→「セル」グループ→「削除」をクリックすることで表示されるメニューの中から「セルの削除」または「行の削除」を選択④すれば、「テーブルの行のみが削除」出来ます。メニュー中の「シートの行を削除」を選択すると「シートの行全体を削除」になってしまうので注意が必要です。
テーブル上でデータ行を挿入
図２

図１、図２はテーブルを絞り込んでいない状態での削除操作でしたが、今回はテーブルらしく「フィルターを使って絞り込んだ行⑤」を削除することを考えます。
削除したい行のみが抽出されるように絞り込んだ⑤後、図３のように、絞り込み行を選択⑥してマウス右クリックで表示されるメニューから「削除」→「シート行全体」を選択しクリック⑦します。
テーブル上でデータ行を挿入
図３

すると、テーブル内の「絞り込んだ行」のみが削除されます。但しこの際は「行全体が削除」されるため、シートの同じ行にあるデータも削除されてしまうので注意が必要です。

２．マクロによるデータ変更処理

テーブルの絞り込みの方法については「ListObjectの絞り込み」の項を参考にして下さい。

今回の、テーブルを絞り込み後に「絞り込んだ行を削除」するマクロは、図４のようなメインのコードから呼び出される実行モジュールの形としています。
  1. '==========　⇩(1)　実行プロシージャを呼び出す側　============
  2. Sub Test()
  4. '　　テーブル絞り込みのコード
  6. 　Call TableDel_1(Sheets("sheet1").ListObjects(1))
  7. End Sub
図４

０３行目辺りでテーブルの絞り込みを行った後、０５行目「Call TableDel_1(Sheets("sheet1").ListObjects(1))」で実行プロシージャを呼び出します。引数として「テーブル（=ListObjectオブジェクト）」を渡します。

２－１．抽出行のセル範囲を取得し、絞り込み解除後に削除

テーブルを絞り込んだ後は、可視行が「削除する行」となっているはずなので、その行のセル範囲を取得してから削除処理するのが図５です。
  1. '==========　⇩(2)　抽出行のセル範囲を取得し、絞り込み解除後に削除　============
  2. Sub TableDel_1(T As ListObject)
  3. 　Dim r As Range 　 '←可視行のセル範囲（複数行に渡ることもあり）
  5. 　On Error Resume Next
  6. 　　Set r = T.DataBodyRange.SpecialCells(xlCellTypeVisible)
  7. 　On Error GoTo 0
  9. 　T.ShowAutoFilter = False
  10. 　T.ShowAutoFilter = True
  12. 　If Not r Is Nothing Then r.Delete
  14. End Sub
図５

１５行目「Set r = T.DataBodyRange.SpecialCells(xlCellTypeVisible)」では、テーブルのデータ範囲（DataBodyRange）の可視行（SpecialCells(xlCellTypeVisible)）を、セル範囲として変数rにセットします。
もし１つも絞り込まれた行がゼロの場合は１５行目の右辺がエラーとなります。またデータの無いテーブルの場合も右辺がエラーとなりますので、１４行目「On Error Resume Next」でエラーをスルーさせます。
エラーが発生した場合は１５行目は処理せずに次の行に移りますので、変数rには何も代入されずに初期値のNothingのまま（r = Nothing）となります。

１８～１９行目は、絞り込みを解除しています。ここでは１８行目「T.ShowAutoFilter = False」でフィルターボタンを一旦削除し、１９行目「T.ShowAutoFilter = True」で復活させています。この一連の動作で絞り込みが解除されます。
他の手段でも絞り込み解除は可能ですが、詳細は「ListObjectの絞り込み解除」を参照して下さい。

２１行目「If Not r Is Nothing Then r.Delete」の前半のIf文（If Not r Is Nothing Then）では、１５行目でエラーが発生しなかったか（＝絞り込んだ結果、可視行が存在したか）を調べ、可視行が存在したのであれば Then以降の「r.Delete」で「可視行のセル範囲を削除」しています。
なお、絞り込みを解除した後に行削除していますので、図１・図２のように「テーブルの行のみが削除」され、テーブル外の同じ行のデータには影響を与えません。

但し絞り込みを元には戻していないので、このプロシージャ実行後は「絞り込みが解除」された状態で終了することになります。

２－２．絞り込み後の可視行をそのまま行削除

手動の図３をマクロで実行した形が図６になります。絞り込んだ可視行を行削除するものです。
  1. '==========　⇩(2)　絞り込み後の可視行をそのまま行削除　============
  2. Sub TableDel_2(T As ListObject)
  4. 　On Error Resume Next
  5. 　　If T.DataBodyRange.SpecialCells(xlCellTypeVisible).Count = 0 Then
  6. 　　　Exit Sub
  7. 　　End If
  8. 　On Error GoTo 0
  10. 　T.DataBodyRange.EntireRow.Delete
  12. End Sub
図６

３４行目「If T.DataBodyRange.SpecialCells(xlCellTypeVisible).Count = 0 Then」では、データ領域の可視行のセル数を数えています。データ領域に可視行がある（＝抽出行が存在する）場合は、セル数は１以上の値となるので３９行目に進めます。

一方、抽出された行が無い（＝可視行が無い）場合には「SpecialCells(xlCellTypeVisible)」の部分でエラーが発生し、よって３４行目のIf文は評価されずに進み、３５行目「Exit Sub」が実行されてプロシージャを抜け出し、「データ削除処理はしない」ことになります。
また、データの無いテーブルの場合はDataBodyRangeが存在しないため、３４行目の中の「DataBodyRange」の部分でエラーが発生し、可視行が無い場合と同様に３５行目「Exit Sub」が実行されてプロシージャを抜け出します。
なお３４行目でエラーが出ても、止まらずに次の３５行目を実行するように、３３行目「On Error Resume Next」でエラーをスルーさせています。

可視行が存在する場合は、３９行目「T.DataBodyRange.EntireRow.Delete」で可視行を行削除します。
ここでEntireRow をつけずに「T.DataBodyRange.Delete」としてしまうと、「行全体を削除しますか？」とのメッセージが出てしまい処理がストップしてしまいますので、EntireRow は必要です。
それでもEntireRowを使いたくない場合は、３９行目の前に「Application.DisplayAlerts = False」を実行して「メッセージを表示させない」ようにする方法もありますが、結果は同じで行全体の削除になります。また、DisplayAlertsプロパティをFalseにした時には、必ず「Application.DisplayAlerts = True」を実行させて、メッセージ表示ONの設定に戻さないと危険です。

また「データ変更時」と同様に「手動での絞り込みの悪影響」は削除時にもあり、「手動絞り込み時には、全行が削除されてしまう」ことになります。
なお、その対策もデータ変更時と同じです。手動絞り込み後に図７のような操作を「マクロ側から」行えば、可視行のみを削除することができます。
No.操作内容
1ﾃｰﾌﾞﾙのSheetをActiveにしてから､ﾃｰﾌﾞﾙの一部または全体をｾﾙ選択
(例:ﾃｰﾌﾞﾙ.Range.Select ､ ﾃｰﾌﾞﾙ.Range.Select)
2非絞込列を絞り込み解除
(例:ﾃｰﾌﾞﾙ.Range.AutoFilter Field:=1)
3非絞込列を無意味に絞り込み
(例:ﾃｰﾌﾞﾙ.Range.AutoFilter Field:=1,Criteria1:=">=1")
図７

また絞り込んだ結果「抽出行が１つも無い状態」で図７の３９行目だけを実行すると、「全データが削除される」ことになりますので、３３～３７行目で抽出行ゼロを検出させ「抽出行がゼロの場合は、削除処理をしない」ようにしています。
なお３９行目の削除処理の前に「図７の処理」をマクロ実行しても、抽出行ゼロ→全データ削除の対策にはなりません。

アプリ実例

「DVD等の内容・保管場所等管理システム」
「先行予約可能な備品予約・貸出システム」