以下のように「.SpecialCells(xlLastCell)」を使うと、
「Ctrl＋End」と同じ挙動となる。
┌──────────────────────────────────────┐
│Cells(1, 1).SpecialCells(xlLastCell)                                        │
└──────────────────────────────────────┘
「Cells(1, 1).SpecialCells(xlLastCell).Row」という風にすると
空行を跨いだ最後の行を調査できるので便利だ。
つまり、「.SpecialCells(xlLastCell)」は
データのある範囲の末尾に行けるということだ。

但し、一度、末尾の方に値を入力すると、それを削除しても
データのある範囲は縮小されない。
ファイルを保存する時に再計算されるのだ。

よって、「.Cells.Clear」だけでは元あったデータの範囲が残ってしまうということだ。

これでは困る場合、ファイルを保存すれば解決する訳だが
そんなことのためにファイルにアクセスしたくはない。

その解決方法の裏技。
「.UsedRange.Row」をシートに対して使ってあげればいい。
ということで、以下のようなダミー処理を実行すればいいらしい。
┌──────────────────────────────────────┐
│ダミー変数 = UsedRange.Row                                                  │
└──────────────────────────────────────┘

ま、たいていは範囲の境目とかを検索する時に再計算したくなるものだから
以下のようにダミーの代入処理をかましてやればいいのかもしれない。
┌──────────────────────────────────────┐
│With ThisWorkbook.ActiveSheet                                               │
│    y = .UsedRange.Row  'ダミー処理                                         │
│    y = .Cells(1, 1).SpecialCells(xlLastCell).Row                           │
│End With                                                                    │
└──────────────────────────────────────┘