Insert a number of rows based on the difference between 2 cell values

[tommyleinen]

I have a main sheet which I use to filter on a specific name. When the filter is applied, cell I1 displays the number of rows of data (inclusive of the 9 rows containing the header row).

Likewise in sheet2, cell T1 displays the number of current rows of data (exclusive of the 9 rows containing the header row).

I want to be able to insert rows equal to the difference between the Sheet1 I1 and Sheet2 T1, in sheet2 beacuse I'm then going to copy and paste the data to match the same area.

I need to insert the rows above the last current row on sheet 2. This is so my sum formula underneath the data area still picks up all the rows above.

So far, to select the bottom row I have:

Rows(Sheets("Sheet2").Range("T1").Value + 9).Select

I am struggling to add the corresponding number of missing rows, can anybody help?

I know what I need to reference but cannot figure out the code.

I'm going to need to apply the same macro to other sheets in the workbook, so would like to keep it as simple as possible. I've been looking at the "Range("6:7").Insert" method

Thanks in advance

 

[BSALV]

Sub test()
     Dim sName, iRow1, iRows

     sName = "blad3"                                            'your sheet
     iRow1 = 10                                                 'start row
     iRows = 20                                                 'number of rows

     Sheets(sName).Cells(iRow1, 1).Resize(iRows).EntireRow.Insert
End Sub

 

[tommyleinen]

Thankyou, that works brilliantly. I added a helper cell (U1) to assist and substitued the example to suit:

iRow1 = Sheets("Sheet2").Range("T1").Value + 9 'start row
iRows = Sheets("Sheet2").Range("U1").Value 'number of rows