和風スパゲティのレシピ

日本語でコーディングするExcelVBA

Endによる最終行取得は非表示の行を検知できない

知らずに落ちると抜け出せなくなる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.Columns.Count + ws処理シート.UsedRange.Column - 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処理シート)

を挟むのを、忘れないようにしてください。


最終行の取得に関する詳しい解説はこちらをどうぞ

www.limecode.jp


おまけ: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

こういうコードを書くようお気を付けください。