Excelが認知している最終行を取得する。

データが登録されている最終行を取得する。

Excelが認知しているセル情報を取得する方法です。最終行の他にも「計算式のあるセル」「表示されているセル」などいろいろ使えます。


Option Explicit

Sub TEST14()
    ' 最終セルを表示
    MsgBox ActiveSheet.Cells.SpecialCells(xlLastCell).Address
End Sub

これを実行すると、
最終セルの取得(実行結果)
(画像をクリックすると、このサンプルがダウンロードできます)
となり、最終セルが取得できることが解ります。このサンプルでは「Address」を取得していますが、「Row」とすれば最終セルの行番号が取得できます。
但し、登録したり消去したりを繰り返しているシートでは、最大でデータが収容された時点の最終セルが認知されていることがあり、現時点での最終セルとは違う場合がありますので、ここで取得した最終セル(最終行)から左上に向かってブランクでないセルを探すようにすると現時点の最終セルが正しく取得できます。

SpecialCells」メソッドの指定は「最終セル」を特定するだけの機能ではありません。
SpecialCellsの機能
カッコ内の定数指示子はVBEでこのように列挙表示されるので、簡単に指定ができます。内容は「最終セル」の他、「コメントのセル」「計算式のセル」「同じ書式のセル」「同じ入力規則のセル」などがいろいろ指定できます。
なお、これらの指定では戻り値は複数のセル範囲が返ってきますので、これを「For Each 〜」でセルに分解して操作する必要があります。(「ダウンロード」の「計算式一覧」ではこの「計算式のセル」を取得してから各セルの計算式を参照しています。)

※ご注意!!
@ 行やセル範囲に対して挿入や消去を行なった場合、SpecialCells(xlLastCell)は誤認する場合があります。縦スクロールバーを下端まで下げた時に空行を表示してしまう時がこれにあたります。この状況はそのブックを保存すると正常に戻ります。
A SpecialCellsメソッドはWorksheet_SelectionChangeイベントを発生させます。
B フィルタで表示行を制限している場合、SpecialCells(xlLastCell)は表示されている中での最終セルを指します。
ここで、上記の「ご注意」の対応を含めて、もう一つの方法(Endプロパティ)を挙げておきます。

Option Explicit

Sub TEST15()
    ' 最終セルを表示(歯抜け不対応)
    MsgBox ActiveSheet.Range("$A$1").End(xlDown).Address
End Sub

どちらが良いということはありません。ケースによって使い分けて下さい。
End」プロパティは、オペレーション上でCtrlキーを押しながら方向キー(↓等)を押した時のジャンプ先のセルを取得します。
上記「ご注意!!」の@についてはEnd」プロパティでは影響を受けず、正しい最終行を取得できると言えます。
BについてはSpecialCells」メソッドと状況は変わりません。ShowAllDataメソッドでフィルタ抽出を解除してから最終行を取得する必要があります。
SpecialCells」メソッドよりEnd」プロパティを先に勧めない理由は、逆に途中に抜け行があるとその前の行を返してしまうからです。画像をクリックしてExcelを開いて動作させると、
最終行の誤認例
(画像をクリックすると、このサンプルがダウンロードできます)
となりますが、実際にはA列には縦に「A」から「Z」が並んでおり、最終行は「Z」がある26行目です。しかし、15行目の「O」が消去されているのでこのような結果になります。従って途中に「歯抜け」がある場合はEnd」プロパティは不向きです。
一方、SpecialCells」メソッドは、End」プロパティのように指定した列の最終行を取得するのではなく、シート上の最終セルとして矩形範囲の右下のセル位置で返します。従って、列によって最終行位置が違う場合はSpecialCells」メソッドで取得した行から上に向かって空行をスキップした上で正しい最終行を取り直す必要があります。

End」プロパティでの「歯抜け」の対応としては、

Option Explicit

Sub TEST15()
    ' 最終セルを表示(歯抜け不対応)
    MsgBox ActiveSheet.Range("$A$1").End(xlDown).Address
End Sub

Sub TEST16()
    ' 最終セルを表示(後ろから探す)
    MsgBox ActiveSheet.Range("$A$65536").End(xlUp).Address
End Sub
この「TEST16」のように「下から探す」方法があります。しかし、これもExcelの限界である65,536行目から探すとすると数百、数千件程度のデータの場合は無駄な気がしてなりませんが決して遅いものではありません。
なお、65,536行全てが埋まっていた場合や、何もデータがない場合は誤認するのでご注意下さい。
オートフィルタの抽出中は最終行を誤認します。   .End(xlDown)」や「.End(xlUp)」は手動操作での「Ctrl+↓」「Ctrl+↑」と同じです。 上記の注意事項に記載したとおりで、どの方法でもオートフィルタの抽出中は、表示行の中で動作するので実際の最終行にならない場合があります。
最終行判定の操作の前にオートフィルタの抽出状態を解除してしまえば正しい最終行が得られます。


    ' フィルタ抽出状態を解除する
    With ActiveSheet
        If .FilterMode Then .ShowAllData
    End With