# For/Next Loop

#### ctc005

##### New Member
Hi all:

I'm told a for/next loop is what I want to use to accomplish this but I don't know where to start. Here goes:

I have a row in my excel, that based on an input I want to be repeated. So let's say the row is row 10 and I want it repeated based on an input in cell E5. The input in cell E5 would be for a "how many" input. So if they put 5 in E5, I need row 10 to be repeated 4 times, plus the original for a total of ten rows. Any help is appreciated

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### Jonmo1

##### MrExcel MVP
How do you get "Repeated 9 times" from user entry of 5 ???

That makes no sense??

#### ctc005

##### New Member
sorry for some reason i had 5 and 10 mixed up. It would be 9 for an input of 10 and 4 for an input of 5. My bad

#### ctc005

##### New Member
I have a row in my excel, that based on an input I want to be repeated. So let's say the row is row 10 and I want it repeated based on an input in cell E5. The input in cell E5 would be for a "how many" input. So if they put 5 in E5, I need row 10 to be repeated 9 times, plus the original for a total of ten rows.
Hello ctc005,
A couple questions to clarify just what you want to do.
1.) What exactly do you mean by "I want it repeated. . . " ?
2.) By "if they put 5 in E5, I need row 10 to be repeated 9 times, plus the original for a
total of ten" - Is this because it was on row 10 to begin with?
(ie, what if the user were on row 15 and they enter a 6 in E5?)

I corrected my original post it should be repeated (ie like a fill down) same function as my doing it by hand but I want a macro to do it so it is user friendly. So if hey input 5, then row 10 would be "autofilled" down four rows for a total of 5, like the user wanted.

#### Jonmo1

##### MrExcel MVP
Code:
``````Sub test()
Range(ActiveCell.Row & ":" & ActiveCell.Row + Range("e5").Value - 1).FillDown
End Sub``````

#### HalfAce

##### MrExcel MVP
Hello ctc005,
What exactly do you mean by "repeated"?
(Copied?) If so, do you want rows inserted (?), or simply copied to the number of rows
entered in E5 (-1) below the active row?

Assuming you want the active row copied to the row(s) below it - (to total the number in E5),
you don't need to use a loop. You can simply use something like:
Code:
``````Sub Demo()
ActiveCell.EntireRow.Copy _
Cells(ActiveCell(2).Row, "A").Resize(Range("E5").Value)
End Sub``````

If I'm on the wrong track then you elaborate a bit?

#### ctc005

##### New Member
Two good tips, I'll play with them a bit. My question to you though is, I forgot to mention this, but I have other information below the existing active row and need it to "insert" the rows rather than just fill down. I think that may make a difference. I would just put them all there and leave them blank if they didn't use them, but with a possibility of 150+ rows it would look really bad and be a waste of time to scroll past all the empty rows. So i'm looking for a way for it to be exactly as long as you need it every time. Hope that makes more sense than my previous posts

#### Jonmo1

##### MrExcel MVP
to INSERT
Code:
``````Sub test()
For i = 1 To Range("E5").Value - 1
Rows(ActiveCell.Row).EntireRow.Copy
Rows(ActiveCell.Row).EntireRow.Insert
Next i
Application.CutCopyMode = False
End Sub``````

#### ctc005

##### New Member
If I want to specifically use row 10, how can I adjust that so it doesn't do the active cell.

#### HalfAce

##### MrExcel MVP
To specify row 10 you can do something like this. (Again, avoids any looping.)
Code:
``````Sub InsertRowsAndFill()
Cells(11, "A").Resize(Range("E5").Value - 1).EntireRow.Insert
Cells(10, "A").EntireRow.Copy Cells(11, "A").Resize(Range("E5").Value - 1)
End Sub``````

Hope it helps.

Replies
2
Views
127
Replies
5
Views
316
Replies
2
Views
402
Replies
3
Views
199
Replies
6
Views
526

1,191,552
Messages
5,987,232
Members
440,086
Latest member
Mahi786

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back