current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question
    
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 Jul 22 '13 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. –  Dick Kusleika Jul 22 '13 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 –  user1305569 Jul 22 '13 at 15:22
add comment

2 Answers

up vote 1 down vote accepted

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.

share|improve this answer
    
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? –  user1305569 Jul 22 '13 at 14:54
    
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. –  David Zemens Jul 22 '13 at 15:06
    
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. –  user1305569 Jul 22 '13 at 15:21
    
The loop will NOT operate on the rows that you add to the range. –  David Zemens Jul 22 '13 at 15:29
    
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? –  user1305569 Jul 22 '13 at 15:43
show 5 more comments
up vote 1 down vote

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
share|improve this answer
    
how will union work with the edit that I added to my original post? –  user1305569 Jul 22 '13 at 15:29
    
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). –  LS_dev Jul 22 '13 at 15:34
    
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? –  user1305569 Jul 22 '13 at 16:00
    
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. –  LS_dev Jul 23 '13 at 7:36
    
then how would I go about taking a row from a range of hidden rows and placing it within a range of unhidden rows? –  user1305569 Jul 24 '13 at 14:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.