Add rows based on multiple cell values

New to VBA but need a simple formula to insert rows based on multiple cell values.

To add context, I'm creating a "rent roll" for commercial property. There are "x" amount of tenants and each tenant has "y" amount of rental increases through out their term.

x >= 1
y >= 0

Row 1 is my Header Row
Row 2, Column 1 is the start of the "x" variable with x amount of Rows
Row 2, Column 2 will have an input cell for each "x" which equals y

The code needs to insert y amount of rows below each x.

Thank you in advance.

Quote

Sadly Formulas cannot insert rows ... :wink:

By formula I meant a simple VBA Sub code as I would think it is relatively easy to do (although due to my lack of experience I am unable to do it!).

I should add I've already done the part to add x tenants:
[KBD]
Sub NumberTenants() Dim StartNumber As Integer
Dim EndNumber As Integer
EndNumber = Range("B1")
Range("B3:B12").Clear
For StartNumber = 1 To EndNumber
Cells(3 + StartNumber, 2).Value = "Tenant " & StartNumber
Next StartNumber
End Sub
[/KBD]

Thanks this helps a bit however I have to keep it a two-step process because the first part determines how many tenants there will be (x) and once that is executed then I would enter in the y variable (NbRows) for each x.

Therefore x₁ will have y₁, x₂ will have y₂, and so on.

I've tried finding it elsewhere and I believe the best way would be to work your way from the bottom entry as to avoid creating a mess around a different amount of rows being inserted from each y.

For example, if there are three tenants (x=3) the first Macro would create those spaces (the macro I give above is a watered down version of the total sheet and the entire macro affects multiple sheets) and I would then input the y variable for each x and therefore have different values. So my thinking is the macro would start with Tenant 3, add the rows below it (say y=2 in this instance), then the loop would look at Tenant 2 and the y value for it, which would only be one row above, then add the rows below it and so on until it gets to the top row.

This would avoid having to take into account that if you start at the top row and then add say two rows, the next input (y₂) to evaluate would be down 1 + y₁ number of rows.

Thanks. Not familiar with that object, will have to look into it.

I was able to figure out a workaround although I feel like it is a bit crude. I had to add the If Else statement as a zero value resulted in a compiling error.

Thanks for your help!

Sub RowInsert()


    Dim StartNumber As Integer
    Dim Tenants As Integer
    Dim RentIncreases As Long
    Dim TotalHeight As Long
        
    Tenants = Range("C45")
    TotalHeight = Tenants
    
    For StartNumber = Tenants To 1 Step -1
        
        RentIncreases = Cells(49 + StartNumber, 11)
        
        If RentIncreases = 0 Then
      
        Else
        
        Cells(49 + StartNumber, 2).Offset(1).Resize(RentIncreases).EntireRow.Insert Shift:=xlShiftDown
        
        TotalHeight = TotalHeight + RentIncreases
              
        End If
        
    Next StartNumber


End Sub