2

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

Improve this question
3
  • 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? Commented Jul 22, 2013 at 13:49
  • 2
    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. Commented Jul 22, 2013 at 14:33
  • 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 Commented Jul 22, 2013 at 15:22

2 Answers 2

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

Forgot to mention I do have it set as a range in the front, but the Resize looks like it is what I am looking for. Could you take a look at my Edited post at the top to see if the resize will handle the same thing?
I think it can be done with Offset and Resize methods, but do heed @Dick Kusleika's comment, it is generally a bad idea and might lead to some undesired results, if you change the range you're looping through, inside the loop.
The reason I am changing the range in the loop is because I am looking for some kind of spill over functionality (kind of like stack overflow) where once the cell in the range reaches its cap then a row is added and that new row starts to fill up, onto infinity.
The loop will NOT operate on the rows that you add to the range.
what if add to the range outside of the loop then have a GoTo command to go back to the beginning of the Loop. how would doing that fit in to spill over kind of thing that I want?
|
3

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
Improve this answer

5 Comments

how will union work with the edit that I added to my original post?
That's not so easy. If both ranges are simple rectangles, you may use Set Range2 = Range2.Resize(Range2.Rows.Count-1, Range2.Columns.Count).Offset(1,0).
the ranges have formulas in the cells within them, is this what you mean by simple rectangles or do you mean the actual cell dimensions?
Not dimensions. Shape of selected cells. You may have a composite range with, eg, 1:10,12:20,30:30: this range is composed by 3 areas (what I called "rectangles"): 1:10 and 12:20 and 30:30. To remove one line from this range, you can not just .Resize.
then how would I go about taking a row from a range of hidden rows and placing it within a range of unhidden rows?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.