10本目「条件付きでの行削除」を学ぶ
基礎知識
行削除 For ~~ Step
今回のお題では、行の削除を最下段から行います。その理由を説明します。
たとえば
1〜6行の表があったとします。そこで2(i)行目、3( i + 1 )行目・・を削除としていきます。
するとエクセルは、消された2行目を詰めてこんな感じになります。
1、2、3、4、5、6 の並びが、
↓ 2( i ) 行目削除
1、3、4、5、6、空 の並びに ここまではOK
↓ 3( i +1) 行目削除
1、3、5、6、空、空 の並びに 3より先に4が消えてしまった!
つまり、For文で上から行をインクリメントしていくと、とびとびで行が削除されてしまうのです。
そのため、削除に関しては、表の下からFor文で回します。iをデクリメントしていきます。
For i = 9999 To 1 Step -1
Next
↑デクリメントはStepを用いてこのように記述します。
プロシージャとモジュールについて
これに関しては今回特別にでてきたものではありませんが。一度、早い段階で知識を整頓しておいたほうが良いとかんじたのでまとめます。
まずは、プロシージャです。
これはSub () ~~~End Subなどのマクロを実行できる最小の実行単位です。
そして、モジュールはこのプロシージャを記述するための場所のことです。
モジュールには様々種類があります。
シートモジュール、ブックモジュール、標準モジュール、Windowsならフォームモジュールがあります。
これまで全てのお題は、この標準モジュールに記述しています。その中にプロシージャを作成し、マクロを実行しています。
プロシージャの中で使える変数
例えば、標準モジュールのモジュール1にtest1,test2二つのプロシージャを記述します。
モジュール1はマクロ処理単位を2つもっている状態です。
test1のプロシージャ内で宣言した変数(文字列型のa)は、test1のプロシージャ内でのみ有効です。
この場合、test2では「文字列a ?そんな変数宣言されていないよ?」とエラーが出てしまいます。
Sub test1() Dim a as String a="macro" End Sub Sub test2() Msgbox a //実行できずエラーが返されます End Sub
では、複数のプロシージャで同じ変数を共有したい場合はどうするかというと、プロシージャ外の宣言セクションで宣言すればいいんです。
実際に記述してみるとこんな感じ
Option Explicit Dim a As String. '宣言セクション モジュールレベル変数(以下モジュール内のプロシージャでどこでも使える) '-------------------------------- Sub test1() a="macro" End Sub Sub test2() Msgbox a //実行可能 End Sub
これでtest1→test2と実行していけば、メッセージボックスがでて「macro」と一言つぶやくマクロができました。
では他のtest3というプロシージャがあったとしたとき、そこからこのtest1,test2を呼び出すには、つまり二つの小っちゃなマクロ処理を合体して、test3プロシージャーから呼び出すにはどうすればよいか、考えます。
call
文字通り、マクロ名を指定してマクロを呼び出す機能がCallです。
実際に使ってみると
Option Explicit Dim a As String. '宣言セクション '-------------------------------- Sub test1() a="macro" End Sub Sub test2() Msgbox a //実行可能 End Sub ’ 以下を追加 Sub test3() Call test1 'マクロ名はSub ○()の○の文字列 Call test2 End Sub
これでtest1,test2 の順にマクロが実行されます。
今回はどちらも短い処理なので有り難みがほぼないですが、このtest1,test2のマクロ処理がそれぞれ複雑な場合、
Callは非常に便利です。どんだけ小分けしたマクロの機能が複雑になろうとも、test3のマクロの内容はずっとこのままです。
あくせく働かされるのはtest1,2で、test3は「まずtest1やってー、おわったらtest2も頼むねー」と中身も見ずに命令するだけです。test3がいやらしい上司に見えてきました、まるで社会の構図のようです笑
要は極端な話、test3で必要なマクロを全て呼び出せば、「test3マクロを一つだけ実行して全て済む」ということです。業務ぽちっと自動化計画の道がまた一歩大きく切り開かれました^^
Callは革命的に便利だと思います。ですが、ただやみくもに機能を小分けしてもよくありません。あくまで何らかの大きな機能を実現するために、全体の流れを掌握しやすくするために意図、目的をもって必要処理をまとめます。(そういう意味でこの例に挙げた処理は、Callするに相応しくは無いです。)
マクロ実行後の元に戻る
※マクロは実行後にCtr+Z(元に戻る)機能が使えません(超大事)。そのため、処理前シートを別シートにペーストして一旦逃しておくことは大変重要かと思います。ということで、お題にはありませんでしたが、Call及び宣言セクションでの定義の復習の意味も込めて、今回は処理前のバックアップの機能を作成し、呼び出します。
回答
Option Explicit Dim ws As Worksheet ’宣言セクションでモジュール変数を定義 ’ーーーーーーーーーーーーーーーーーーーーー Sub 行削除() Application.ScreenUpdating = False Set ws = Worksheets("受注") Call バックアップ Dim i As Long For i = ws.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1 If ws.Cells(i, 3) = "" Then If ws.Cells(i, 4).Value Like "*不要*" Or _ ws.Cells(i, 4).Value Like "*削除*" Then ws.Rows(i).Delete End If End If Next Application.ScreenUpdating = True End Sub ’ーーーーーーーーーーーーーーーーーーーーーー Sub バックアップ() Dim wsBak As Worksheet Set wsBak = Worksheets.Add(after:=ws) On Error Resume Next Application.DisplayAlerts = False Worksheets(ws.Name & "(bak)").Delete Application.DisplayAlerts = True On Error GoTo 0 wsBak.Name = ws.Name & "(bak)" ws.Range("A1").CurrentRegion.Copy wsBak.Range("A1") End Sub
バックアップ機能が別枠で下に持ってきた為、本題のFor文の処理が読みやすく、可読性が少しよくなった気がします。
今後もこの視点は忘れず持ちながらプログラムを組みたいです。
学んだこと総括
行の削除は下から For 〜〜 Step -1
処理前シートを別シートに逃しておく バックアップ作成(マクロ実行後は戻れない為)
モジュールで共有する変数は、宣言セクションで宣言
Call マクロ名 で好きなタイミングで別プロシージャ処理を呼べる