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

```
range.row
```

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

```
range.column
```

returns the column where the range starts.

Selecting a cell or a row within the given range

```
range.row(2)
```

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

```
myrow.row
```

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

Ydalir

## Best Solution

Can confirm this behavior:

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)