Clever Geek Handbook

Syntax for defining a whole series as a range

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

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 with a whole line :

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

I am not getting individual cells in the range. My workaround:

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

I can't believe this is the easiest way to make a range ........... any suggestions

+3


source to share


2 answers


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 limited to that range only.

+7


source


String equivalent

Set r = Range("A:A")

will be



Set r = Range("1:1")

I was curious and checked these tests based on Rory's answer. Maybe someone else can explain that the addresses are the same but the numbers are 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
+2


source






More articles:


All Articles