Excel – selecting a range within a range


I am using following VBA code (MS Excel 2010) to select a range of cells within a given range, to copy and insert the copied cells back into the source range:
The range starts in the worksheet at row 2, down to row 2200 and from column 50 to 65.

Set rngFEA = shtTarget.range("myrange")
iMaxLines = 20
With rngFEA
.Range(Cells(3, 1), Cells(3 + iMaxLines, .Columns.Count)).Copy
.Range(Cells(3, 1), Cells(3 + iMaxLines, .Columns.Count)).Insert Shift:=xlDown
End With

Doing it this way (no reference to rngFEA for the Cells(Row,Column) parameter) works fine, the selected cells are part of the range as expected.

I don't like use no reference for the cells() parameter, because using no reference makes the cells referring to the worksheet and rather give erroneous results, so I would rather like to use rngFEA.cells():

Set rngFEA = shtTarget.range("myrange")
iMaxLines = 20
With rngFEA
.Range(.Cells(3, 1), .Cells(3 + iMaxLines, .Columns.Count)).Copy
.Range(.Cells(3, 1), .Cells(3 + iMaxLines, .Columns.Count)).Insert Shift:=xlDown
End With

BUT the resulting range lies well OUTSIDE the range rngFEA, somewhere off to the left and down.
I even could not find a relation between the used indexes and the resulting offset.

I believe the range.insert could also be stated as

rngFEA.cells(3,1).insert shift:=xldown

but this is not my concern right now.

I am well aware of the difference of referencing or not, but i do not understand why NOT using the reference to the range gives a correct result, and using the reference does not.

I expect

rngFEA.range(rngFEA.cells(1,1), rngFEA.cells(10,10))

to return the range of the topmost, leftmost cell of the given range down to the tenth cell to the right and down in the same range.
In the example code I select the leftmost cell in row 3 within the given range down to the 23rd cell and the right end of the range.
(the actually selected row is row 3 in the workbook and hence row 2 in the range)
I looked into Microsofts Informations and several forums, but could not find an explanation which describes this effect sufficiently.

I know that


returns the number of the row in the WORKSHEET where the range starts,


returns the column where the range starts.

Selecting a cell or a row within the given range


does not return the second row of the range but the second row of the worksheet. A

for each myrow in range.rows

the index


returns the number of the row within the range, but using it as the selecting index seems to return the row within the worksheet, so I need to add

range.row + myrow.row

to index to the actual row within the range.

The mechanics behind this and the above described behaviors of selecting a range within a range are confusing to me.
Since there are many ways to handle things in Excel using VBA, I hope you can give me a general explanation of the described behavior rather than a solution (if not for explaining the why) 🙂

THX in advance


Best Solution

Can confirm this behavior:

Sub Tester()

    Dim rng As Range

    Set rng = Range("C3:H28")

    'This selects E5:F6 (???)
    With rng
        .Range(.Cells(1, 1), .Cells(2, 2)).Select
    End With

    'This selects C3:D4 (expected)
    With rng
        rng.Parent.Range(.Cells(1, 1), .Cells(2, 2)).Select
    End With

End Sub

Seems like it may be related to the "double relative" combination of using both .Range and .Cells

Instead using rng.Parent.Range and having only the .Cells be relative to the containing range seems to fix it (and still allows for fully-qualified range references)

Related Question