知らずに落ちると抜け出せなくなるVBAの落とし穴です。
- すべての行をループしたつもりが途中で処理が途切れる
- 最終行がなぜか小さい値になる
- オートフィルターをかけた時だけマクロがうまく動かない
あたりにお悩みの方は、この落とし穴に落ちていないかご確認ください。
Endプロパティは非表示セルを通過する
最終行取得によく使われるEndプロパティさんには、
その知名度の割にあまり知られていない罠があります。
タイトルの通り、このような表↓
で、
MsgBox Cells(Rows.Count, 2).End(xlUp).Row
を実行してみると、「6」が表示されてしまいます。
7,8行目に値が入っていたとしても、Endプロパティは非表示セルを通過してしまいますので、表示されている最終行である「6」しか取得できません。
そもそもEndプロパティは手作業の「Ctrl+↑」ですからね。
こんな表でCtrl+↑を押してみると、
非表示セルでは停止せず、次の表示セルまでジャンプするのが確認できます。
この罠は結構厄介で、
- 自分が作るマクロにはオートフィルターを使うコードが無い
- マクロを渡した相手がたまたま別業務でフィルターを使って罠発動
- フィルター中でも最後の行が表示されているときは罠が発動しない
と、「遅れて発動かつ発動率が100%ではないバグ」を招きやすいので、
原因の特定が難しい部類に入ります。
いざ起きてからでは原因を特定するのが難しいバグに対しては、
先に知識を持っているかどうかが勝負になります。
この仕様はしっかり覚えておきましょう。
解決策
解決策自体は簡単で、
◇ 手作業の非表示を解除する
ws処理シート.Cells.EntireRow.Hidden = False
◇ オートフィルターをクリアする
If ws処理シート.AutoFilterMode = True Then If ws処理シート.AutoFilter.FilterMode = True Then ws処理シート.ShowAllData End If End If
この2つをマクロの開始直後に実行するようにしておけばOKです。
このコードを暗記するのは面倒(特にフィルターのクリア)なので、
Sub すべての行を表示する(ws As Worksheet) ws.Cells.EntireRow.Hidden = False If ws.AutoFilterMode = True Then If ws.AutoFilter.FilterMode = True Then ws.ShowAllData End If End If End Sub
という関数を作って、
Call すべての行を表示する(ws処理シート) Dim LastR As Long LastR = ws処理シート.Cells(ws処理シート.Rows.Count, 2).End(xlUp).Row
とするのが楽ですね。
ただし、これは「すでにEndで書かれたコードのバグを直す」場合の解決策です。
これから書くコードについては、Endを使用しないことも検討してください。
最終行取得にはいろいろな方法があり、
まず一番は、汎用性と安全性のバランスがいいUsedRange↓
LastR = ws処理シート.UsedRange.Rows.Count + ws処理シート.UsedRange.Row- 1
今回のコードに最も適した方法となると、
ストレートにオートフィルターのエリアを取得するAutoFilter.Range↓
LastR = ws処理シート.AutoFilter.Range.Rows.Count + ws処理シート.AutoFilter.Range.Row - 1
あたりを使った方が良いです。
最終行の取得方法の中で、Endさんが一番有名な理由は、
「ActiveSheetを省略できるから」と推測します。
LastR = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1 LastR = Cells(Rows.Count, 1).End(xlUp).Row
↑このように、初学者が書く「Range、Cellsの親シートを省略したコード」と親和性がいいからなんじゃないかと思います。
※ このコードでUsedRangeのActiveSheetを省略するとエラーになります。
が、そもそもRange、Cellsの親シートを省略すること自体が、
ユーザーのクリックひとつでバグを呼ぶ罠ですからね。
Range、Cellsの親シート省略を卒業するときに、
一緒にEndさん一辺倒の最終行取得も卒業しましょう。
もちろん「指定列の最終行を取得する」のはEndさんの得意とするところですので、
- データ全体としての最終行 ⇒ UsedRangeプロパティ
- ある列に限定した最終行 ⇒ Endプロパティ
- あるオブジェクトの最終行 ⇒ そのオブジェクトのセル範囲
のように使い分けるのがいいと思います。
使い分けた上でEndさんにお願いしたい時は、依頼前に
Call すべての行を表示する(ws処理シート)
を挟むのを、忘れないようにしてください。
最終行の取得に関する詳しい解説はこちらをどうぞ
おまけ:Endプロパティのもう一つの罠
さきほどEndさんが有名な理由はActiveSheetを省略できる為と推測しました↓
LastR = Cells(Rows.Count, 1).End(xlUp).Row
さてこれをActiveSheetを省略せず、親シートを指定するときに、
LastR = ws処理シート.Cells(Rows.Count, 1).End(xlUp).Row
と書いている方をたまに見かけます。
何が間違っているかわかりますか?
Rows.Countに親シートが無いんですね。
これだと、「処理シートの最後のセル」ではなく、
「処理シートのActiveSheetの最後のセルと同じ行数のセル」
というコードになります。
まあこれでバグになることはめったになく、
2003年版の互換モードでエラーで止まるくらいです。
もしそうなったとしても、コードを直すより先に、
いまだに2003年版を使っていることをどうにかしてください。
ですが、今は良くても2025年版で最大行が1億くらいになって、
このコードでは最終行が取れなくなるかもしれませんからね。
ちゃんと
LastR = ws処理シート.Cells(ws処理シート.Rows.Count, 1).End(xlUp).Row
こういうコードを書くようお気を付けください。