データが登録されている最終行を取得するのは、何通りかの方法があります。

Excelが認知しているセル情報を取得する方法

「SpecialCells」メソッドを使います。

'***************************************************************************************************
'   最終行の取得サンプル①                                          Module1(Module)
'
'   作成者:井上治  URL:https://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'***************************************************************************************************
'変更日付 Rev  変更履歴内容------------------------------------------------------------------------>
'03/07/25(1.00)新規作成
'03/11/16(1.01)初回修正
'20/02/22(1.10)*.xlsm化、他
'***************************************************************************************************
Option Explicit

'***************************************************************************************************
'   ■■■ ワークシート側からの呼び出し処理 ■■■
'***************************************************************************************************
'* 処理名　：GetLastCell1
'* 機能　　：最終セルの表示①
'---------------------------------------------------------------------------------------------------
'* 返り値　：(なし)
'* 引数　　：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日　：2003年07月25日
'* 作成者　：井上　治
'* 更新日　：2020年02月22日
'* 更新者　：井上　治
'* 機能説明：
'* 注意事項：
'***************************************************************************************************
Sub GetLastCell1()
    '-----------------------------------------------------------------------------------------------
    Dim objSh As Worksheet                                          ' 現在シート
    Set objSh = ActiveSheet
    ' 最終セルを表示
    MsgBox objSh.Cells.SpecialCells(xlLastCell).Address
End Sub

'----------------------------------------<< End of Source >>----------------------------------------

このように「xlLastCell」を引数に指定すると「最終セル」が取得できます。



これを実行すると、

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






※ご注意！！

①	「データが登録されている最終行」という意味では「誤解」があり、Excelからすると「使われている最終セル」のようです。 例えば、このシートで入力範囲より広く罫線を引いてから同じ処理をすると、罫線を引いた最終セルが表示されてしまいます。
②	行やセル範囲に対して挿入や消去を行なった場合、SpecialCells(xlLastCell)は誤認する場合があります。縦スクロールバーを下端まで下げた時に空行を表示してしまう時がこれにあたります。この状況はそのブックを保存すると正常に戻ります。
③	SpecialCellsメソッドはWorksheet_SelectionChangeイベントを発生させます。
④	フィルタで表示行を制限している場合、SpecialCells(xlLastCell)は表示されている中での最終セルを指します。 一時的にフィルタを解除させる方法は一番下のコラムをご覧下さい。

なお、「SpecialCells」メソッドは「最終セル」を特定するだけの機能ではありません。

カッコ内の定数指示子はVBEでこのように列挙表示されるので、簡単に指定ができます。内容は「最終セル」の他、「コメントのセル」「計算式のセル」「同じ書式のセル」「同じ入力規則のセル」などがいろいろ指定できます。

値が入っているセルを上から探す方法

「Endプロパティ」を使って上からデータが入力された最終行を探します。

'***************************************************************************************************
'   最終行の取得サンプル②                                          Module2(Module)
'
'   作成者:井上治  URL:https://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'***************************************************************************************************
'変更日付 Rev  変更履歴内容------------------------------------------------------------------------>
'03/07/25(1.00)新規作成
'03/11/16(1.01)初回修正
'20/02/22(1.10)*.xlsm化、他
'***************************************************************************************************
Option Explicit

'***************************************************************************************************
'   ■■■ ワークシート側からの呼び出し処理 ■■■
'***************************************************************************************************
'* 処理名　：GetLastCell2
'* 機能　　：最終セルの表示②
'---------------------------------------------------------------------------------------------------
'* 返り値　：(なし)
'* 引数　　：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日　：2003年07月25日
'* 作成者　：井上　治
'* 更新日　：2020年02月22日
'* 更新者　：井上　治
'* 機能説明：A1セルから下に向かって空白セルの手前を取得
'* 注意事項：
'***************************************************************************************************
Sub GetLastCell2()
    '-----------------------------------------------------------------------------------------------
    Dim objSh As Worksheet                                          ' 現在シート
    Set objSh = ActiveSheet
    ' 最終セルを表示
    MsgBox objSh.Range("$A$1").End(xlDown).Address
End Sub

'----------------------------------------<< End of Source >>----------------------------------------

この方法は、A1セルを選択した状態でCtrl＋↓を押した状態で選択されたセルのアドレスを取得します。



これを実行すると、

(画像をクリックすると、このサンプルがダウンロードできます)
となりますが、実際にはA列には縦に「A」から「Z」が並んでおり、最終行は「Z」がある26行目です。しかし、15行目が空欄になっているため、その手前のA14セルを返してきてしまいます。



また、フィルタ抽出中は表示されている中で最終セルを探してしまう動作も同様です。

値が入っているセルを下から探す方法

「Endプロパティ」を使って下からデータが入力された最終行を探します。
下からであれば、最初にデータが見つかったセルが「最終行のセル」と見なせるでしょう。

'***************************************************************************************************
'   最終行の取得サンプル③                                          Module3(Module)
'
'   作成者:井上治  URL:https://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'***************************************************************************************************
'変更日付 Rev  変更履歴内容------------------------------------------------------------------------>
'03/07/25(1.00)新規作成
'03/11/16(1.01)初回修正
'20/02/22(1.10)*.xlsm化、他
'***************************************************************************************************
Option Explicit

'***************************************************************************************************
'   ■■■ ワークシート側からの呼び出し処理 ■■■
'***************************************************************************************************
'* 処理名　：GetLastCell3
'* 機能　　：最終セルの表示③
'---------------------------------------------------------------------------------------------------
'* 返り値　：(なし)
'* 引数　　：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日　：2003年07月25日
'* 作成者　：井上　治
'* 更新日　：2020年02月22日
'* 更新者　：井上　治
'* 機能説明：A列最終行から上に向かって空白でない最初に見つかったセルを取得
'* 注意事項：
'***************************************************************************************************
Sub GetLastCell3()
    '-----------------------------------------------------------------------------------------------
    Dim objSh As Worksheet                                          ' 現在シート
    Set objSh = ActiveSheet
    ' 最終セルを表示
    With objSh
        MsgBox .Range("$A$" & .Rows.Count).End(xlUp).Address
    End With
End Sub

'----------------------------------------<< End of Source >>----------------------------------------

この方法は、A1048576セルを選択した状態でCtrl＋↑を押した状態で選択されたセルのアドレスを取得します。



このサンプルであれば「$A$26」セルが返るので「正解」なのですが、 ケ－スによっては上記の方法と同様に「不正解」になります。



まずはフィルタ抽出中は表示されている中で最終セルを探してしまう件です。最後の方に非表示行がある場合は「誤認」されてしまいます。
また、1,048,576行全てが埋まっていた場合は「$A$1」セルを返してしまいます。 (運用上であり得ないと思いますが)

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