2

Consider the following VBA MWE

Sub test()

Dim rng As Range
Set rng = Range("A:A, C:C")

Dim rRow As Range

For i = 1 To 5
    Set rRow = Intersect(rng, rng.Cells(i, 1).EntireRow)
    rRow.Value = 1
    rRow.Cells(, 2).Value = 2
Next
End Sub

Which produces an output that looks like this

1   2   1
1   2   1
1   2   1
1   2   1
1   2   1

As we can see, the line rRow.Value = 1 sets the cells in the first and third column to 1. Now, I can't get my head around why the rRow.Cells(1,2) doesn't access the third column such that the output is 

1       2
1       2
1       2
1       2
1       2

and leave the second column empty, since this appears to be what is happening in the line rRow.Value = 1. Can someone explain this logic to me?

EDIT:

Commenting out the rRow.Cells(,2).Value = 2 such that the code reads

Sub test()

Dim rng As Range
Set rng = Range("A:A, C:C")

Dim rRow As Range

For i = 1 To 5
    Set rRow = Intersect(rng, rng.Cells(i, 1).EntireRow)
    rRow.Value = 1
    'rRow.Cells(, 2).Value = 2
Next


End Sub

yields the following output

1       1
1       1
1       1
1       1
1       1

where columns A and C are filled with ones, and column B is left alone.

Improve this question
6
  • I was expecting the rng object indexes to reflect on the entire definition of the object, defining A as the first column and C as the second, such that it would differ from Range("A1:C5"). Commented Oct 8, 2015 at 8:18
  • Nope.Cells() always returns a single cell, in any context whasoever. rng.Cells(row, col) returns the single cell at (row, col) within the range, couting from top to down for the row and from left to right for the col, relatively to the top and left of the range. Commented Oct 8, 2015 at 8:21
  • Then how does rRow.Value in the above example access only row 1 and 3? Commented Oct 8, 2015 at 8:26
  • No, it modified all the cells of the range rRow. But later, you overwrote the second cell in the next statement :) Commented Oct 8, 2015 at 8:28
  • 1
    Ah ok, that is because your definition of the rRow excluded the column B. If you want it to include the columns A, B and C, you should define it as Range("A:C"). But again, dont be surprized that referencing the cells(, 2) yields a cell that is second to the left of the range. the cells method just Range.Cells computes relatively to the TopLeft of a range, doesnt check if the result is within the range. Commented Oct 8, 2015 at 8:38

1 Answer 1

Reset to default
3

Using the Range or Cells property of a Range object (rather than the more usual Worksheet), provides a reference to a range relative to the top left cell of the original range. It is not in any way restricted to cells within that original range. Hence this:

Range("A1").Range("B2")

refers to the cell one column to the right and one row below A1. So does this:

Range("A1:A10").Range("B2")

Note that it still only refers to one cell, even though the original range was 10 cells. Cells works in exactly the same way (just as it does with a Worksheet parent). So this:

Range("A1").Cells(2, 2)

and this:

Range("A1:A10").Cells(2, 2)

both refer to B2.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Is it possible to define a Range or any other object that would have index properties from the object "contents" rather than the reference, as you described? For instance, such that the index 1 is column A, index 2 is column C and index 3 is out of bounds?
No. The only way to restrict yourself to the original range is to use a For each cell in range.cells loop.
Thanks. I had hoped to avoid a loop, but this is answers my question :-)
Depending on what you are doing, you can loop through the Areas of the range, load each into an array, process it, and dump it back out again.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.