The method is to use from help is

Returns a Range object that represents the cell at the end of th
region that contains the source range. Equivalent to pressing END+U
ARROW, END+
DOWN ARROW, END+LEFT ARROW, or END+RIGHT ARROW. Read-only.

Syntax

expression.End(Direction)

expression Required. An expression that returns a Range object.

Direction Required Long. The direction in which to move. Can be on
of the following XlDirection constants: xlToLeft, xlToRight, xlUp, o
xlDown

--
Message posted from http://www.ExcelForum.com