I am trying to build a macro which should insert specified no. of rows below specified row number. Here's what I've tried so far :

Sub Macro2()
Dim iRow As Long
Dim iCount As Long
Dim i, j As Long
Dim length As Long

arrVal = Array(2, 3, 4) 'no. of rows to add
arrTar = Array(18, 19, 20) 'target row numbers to respectively add the no. rows specified above
length = 3 'length of above array

For j = 1 To length
    iCount = arrVal(j)
    iRow = arrTar(j)
    For i = 1 To iCount
        Rows(iRow).EntireRow.Insert
    Next i
Next j
End Sub

The above code is inserting all the rows it's supposed to add (2 3 4=9) directly below first row no. (18). What is wrong with my code? Again, all I want to do is add specified no. of rows below specified row no. (as per arrays in my code, 2 rows below 18th row, 3 below 19th, etc.)

I have just started with loops, so I am pretty confused on what to do here.

CodePudding user response：

Please, test the next adapted code:

Sub InsertRows_Arrays()
 Dim sh As Worksheet, iRow As Long, iCount As Long
 Dim arrVal, arrTar, i As Long, j As Long, length As Long

 Set sh = ActiveSheet
 arrVal = Array(2, 3, 4)    'no. of rows to add
 arrTar = Array(18, 19, 20) 'target row numbers to respectively add the no. rows specified above
 length = UBound(arrVal)    'length of above array, in fact is 2. A 1D array is zero based

 For j = LBound(arrVal) To length
    iCount = arrVal(UBound(arrVal) - j): iRow = arrTar(UBound(arrTar) - j)
    For i = 1 To iCount
        sh.rows(iRow   1).EntireRow.Insert xlUp
    Next i
 Next j
End Sub

1. 1D arrays are 0 based, except the case when you have on top of the module Option Base 1. I used Ubound to make it working for both cases.

2. What was row 20 before the first insert becomes 22 after first insertions and 27 after the next three. That's why the above code starts insertions from the last array element and of course, uses the correspondent number of rows from the other array...

Please, test it and send some feedback.