First and Last cell of a named range

[Steven K]

Hello,

How do you determine what the first and last cell of a named range and get
its value? Can you use the offset function with this?

 

[Jim Cone]

Steven,
If your named range is named "MyRange" then this is one way...
'-------------------------------
Sub GetNamedRangeValues()
Dim x As Long
Dim y As Variant

x = Range("MyRange").Count
y = "Last cell value is " & Range("MyRange")(x).Value
MsgBox y

y = Range("MyRange")(1).Value
MsgBox "First cell value is " & y
End Sub
'-----------------------------------

Regards,
Jim Cone
San Francisco, CA

 

[Norman Jones]

Hi Steven,

Assuming that the named range is a single area range:

Sub Tester03()
Dim Rng As Range
Dim FirstCell As Range, LastCell As Range

Set Rng = Range("A1:H2")
Set FirstCell = Rng(1)
Set LastCell = Rng(Rng.Count)

'Return address and values of first and last cells
MsgBox "The first cell (" & FirstCell.Address & ") = " _
& FirstCell.value & vbNewLine & _
"The last cell (" & LastCell.Address _
& ") = " & LastCell.value
End Sub