2022/10/15

ListObjectのデータ削除

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

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

1.テーブルに対するデータ削除処理(手動)

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


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

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


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


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

2.マクロによるデータ変更処理

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

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


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

2-1.抽出行のセル範囲を取得し、絞り込み解除後に削除

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


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

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

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

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

2-2.絞り込み後の可視行をそのまま行削除

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


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

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

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

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


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

アプリ実例

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