Adding an Offset Row to a Given Range. Excel VBA

2
votes

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

2
excelvbaauto-increment
So once your criteria is met, you would basically like to concatenate the value of the current cell and the value of the cell directly below it? Do you then wish to keep the value of the cell directly below it intact and skip to the next cell? Or do you wish to delete that row? – rwisch45
It's a bad idea to change the range you're looping through inside the loop. I think a Do Loop is probably more appropriate, but I can't tell why you want to increase it so it's hard to say. – Dick Kusleika
The reason I am changing it in the loop is because I am trying to have a spillover type thing happening. So once a cell in the range reaches a cap then it adds a new cell to the range and starts filling up that cell to its cap, and so on and so forth – user1305569

2 Answers

4
votes

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.

3
votes

You may also use

Set myRows = Union(myRows, myRows.Offset(1, 0))

EDIT:

You asked for it!

To remove one row from generic range

Set myRows = RemoveRowFromRange(myRows, 10)

...

Function RemoveRowFromRange(ByVal Range As Range, row_number As Long) As Range
    With Range.Worksheet
        Select Case row_number
        Case 1
            Set Range = Intersect(Range, .Range(.Rows(2), .Rows(.Rows.Count)))
        Case .Rows.Count
            Set Range = Intersect(Range, .Range(.Rows(1), .Rows(.Rows.Count - 1)))
        Case Else
            Set Range = Union(Intersect(Range, .Range(.Rows(1), .Rows(row_number - 1))), Intersect(Range, .Range(.Rows(row_number + 1), .Rows(.Rows.Count))))
        End Select
    End With
    Set RemoveRowFromRange = Range
End Function