CODEWITHSUNDEEP
excelvba
Gary's Student
Gary's Student

Reputation: 96763

Syntax to Define an Entire Row as Range

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??

Upvotes: 5

Views: 38509

Answers (2)

Sobigen
Sobigen

Reputation: 2169

The row equivalent to 

Set r = Range("A:A")

would be 

Set r = Range("1:1")

I was curious and ran these tests building on Rory's answer. Maybe someone else can explain the addresses being the same and the counts being different.

Sub test()
    Debug.Print Me.Range("A:A").Count '1048576
    Debug.Print Me.Columns(1).Count '1
    Debug.Print Me.Columns(1).Cells.Count '1048576

    Debug.Print Me.Range("1:1").Count '16384
    Debug.Print Me.Rows(1).Count '1
    Debug.Print Me.Rows(1).Cells.Count '16384

    'interseting to me since the counts are different but the addresses are the same
    Debug.Print Me.Range("1:1").Address '$1:$1
    Debug.Print Me.Rows(1).Address '$1:$1
    Debug.Print Me.Rows(1).Cells.Address '$1:$1
End Sub

Upvotes: 7

Rory
Rory

Reputation: 34065

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.

Upvotes: 10

Related Questions