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:
    Sub NumberTenants() Dim StartNumber As Integer
    Dim EndNumber As Integer
    EndNumber = Range("B1")
    For StartNumber = 1 To EndNumber
    Cells(3 + StartNumber, 2).Value = "Tenant " & StartNumber
    Next StartNumber
    End Sub

  • 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 x1 will have y1, x2 will have y2, 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 (y2) to evaluate would be down 1 + y1 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!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!