Excel VBA - Take First Row from one Range and give it to the end of another Range. Array?

Question

I have 2 range variables. I want to have a loop that takes the first row of one range variable and add it to the end of the other range variable.

ie:

rng1 = Range("1:10")
rng2 = Range("11:20")

I want to take row 11 from rng2 and give it to rng1 so that the result is:

rng1 = Range("1:11")
rng2 = Range("12:20")

and each time the loop runs it will take from the top of rng2 and give to the end of rng1.

I was trying to do this with Union or Resize but its not working the way I need it to. In Java I know this can be done through the use of Arrays but im not sure how that would be layed out in VBA.

Set rng1 = rng1.Resize(rng1.Rows.Count + 1, rng1.Columns.Count)
Set rng2 = rng2.Resize(rng2.Rows.Count - 1, rng2.Columns.Count)

any suggestions would be greatly appriciated.

Answer 1

Resize modifies the size of the range (and thus the last row, in this case). It does not work in the second line because you don't want to change the last row, but the starting one; you can just redefine rng2 to meet the expected conditions.

Dim rowsToAdd As Integer: rowsToAdd = 1
Dim colName As String: colName = "A"
Set rng1 = rng1.Resize(rng1.Rows.Count + rowsToAdd, rng1.Columns.Count)
Set rng2 = Range(colName & CStr(rng2.Row + rowsToAdd) & ":" & colName & CStr(rng2.Row + rng2.Rows.Count - 1))

This code delivers the functionality shown in your example, that is: increases rng1 last row by rowsToAdd and, in parallel, redefines rng2 such that rowsToAdd is added to its first row. The last row of rng2 is maintained unaltered.

Bear in mind that this is a more adaptable structure than just "taking the row from this range and putting it into this other one"; it expect a variable name of rows to be addded to the last row of rng1 and to the first row of rng2.