Syntax to Define an Entire Row as Range

Question

In Excel, I can define an entire column as a range and address individual cells in that range like:

Sub headache()
   Dim r As Range
   Set r = Range("A:A")
   MsgBox r(1).Address & vbCrLf & r(2).Address
End Sub

If I try to do the same thing with an entire row:

Sub headache2()
   Dim r As Range
   Set r = Rows(1)
   MsgBox r(1).Address & vbCrLf & r(2).Address
End Sub

I do not get the individual cells in the range. My workaround is:

Set r = Range(Cells(1, 1), Cells(1, Columns.Count))

I can't believe this is the simplest way to make the range...........any suggestions??

Answer 1

You can use:

   Set r = Rows(1).Cells

or just use:

   MsgBox r.Cells(1).Address & vbCrLf & r.Cells(2).Address

but note that accessing the Range (or Cells) property of a range using an index will never be restricted to that range alone.