selecting a range within a range

Question

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

Answer 1

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)

Answer 2

I encountered the same behavior when getting an Excel range within a range in VB.Net. Tim's answer solved the weird behavior. At first I though it had something to do with the use of With but I guess it had something to do with the double relative referencing of dot notation as Tim had suggested.

Public Sub SomeMergingFunction(ByRef inputRange As Excel.Range)
    With inputRange
        Debug.Print(.Address) ' $A$4:$A$130 correct
        Debug.Print(.Cells(1, 1).Address) ' $A$4 correct
        Debug.Print(.Range(.Cells(1, 1), .Cells(1, 1)).Address) ' $A$7 wrong
        Debug.Print(.Parent.Range(.Cells(1, 1), .Cells(1, 1)).Address) ' $A$4 correct
    End With
End Sub