Inserting a row x number of times

[alamode]

Hello. Its my first post here !

I need some help in VBA. Just started recently using it and finding it to be of great help. Im stuck on one problem though.

I need to insert rows in a sheet a certain number of times. To insert rows i used this command:

Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove

Which works alright. Problem is it runs only once. I need to run it multiple times.. So i did this:

For i = 1 To 19
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Next i

And again it works alright.. But the problem is, I dont know exactly how many times im going to repeat this command. It is calculated in cell I10. I have a formula set in sheet1, Cell I10 which tells me that i need 19 rows. So i was hoping i could link this cell up so that it would repeat this command as many times as the value in Sheet1,Cell I10 is. Cant get to do this though.

Help me out !

Regards!

 

[alamode]

Nevermind !! I got it.. This code got it working !

A:
If Worksheets("Details for autocreation").Range("I10").Value > I Then
Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
I = I + 1
GoTo A
Else
End If
End Sub

 

[John_w]

I hate to see a VBA beginner using the GoTo statement as it can always be avoided. You were nearly there with your For loop. Try this:

    Dim i As Integer
    
    For i = 1 To Range("I10").Value
        Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
    Next

Or without a loop, using the Resize method:

    Selection.Resize(Range("I10").Value, 1).EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove

 

[alamode]

Thanks !!!