Search code examples
excelvbaauto-increment

Adding an Offset Row to a Given Range. Excel VBA

I have a variable which at the beginning is set to a given range.

I want to have a loop statement that would take the next row down from the end of the given range and add it to that range.

ie:

myRows = Range(1:10)

    For Each cell in myRows

       If cell.Value > 2048 Then

           myRows = myRows + myRows.Offset(1, 0) ---This is where i need help---

Basically how do i auto increment the range each time the loop runs.

Edit:

Also how would I Add to the front of the range. As well as Take away from the back of the range.

ie

Range(1:10) is unhidden Range(11:20) is hidden

I want to add one to the unhidden range which would take away from the hidden range so its:

Range(1:11) is unhidden Range(12:20) is hidden

Solution

  • You have myRows as a Variant data type. You need to declare it as a Range object.

    Dim myRows as Range

    Then you would need to use the Set keyword to assign a Range object).

    Set myRows = Range("1:10")

    Then, use the range .Resize method:

    Set myRows = myRows.Resize(myRows.Rows.Count+1, myRows.Columns.Count)

    Otherwise, if you need to maintain myRows as type Variant, let me know and I can re-work this.