Add multiple rows in Excel starting from same cell value VBA

Question

I have a sheet that has multiple blocks of recurring rows. Something like this:

SALAD
Small
Medium
Large

FRIES
Small
Medium
Large

BURGERS
Small
Medium
Large

How can I add, say, 10 rows in between "Medium" and "Large" in each section?

My sheet has hundreds of these types of sections, so I can't do it manually.

If you want to just add one row, I can use the control + F, search "Large," select all, and add a row. But I'd have to do that each time for each row I want to add, 10 times in total.

Thanks!

This is a great opportunity to learn VBA - Turn on the macro recorder. Then, do a find for "Medium", and add 10 rows below. Then, do it again. Then, stop the recorder and you'll have a skeleton of what to do. Have a think on that, and check out [how to avoid using `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) and try your best. You can then try and set up a simple loop (google something like "VBA loops" or "VBA for loop"). Then, come back and let us know where you're stuck! (showing the code, of course) :D — BruceWayne, Nov 30 '16 at 23:34

score 1 · Answer 1 · answered Dec 02 '16 at 23:20

You can easily do this with VBA. To get to the VBA editor, press ALT+F11 when in Excel. The create a new module (Insert>Module) and paste the following code:

Sub insertRows()

    Dim vcell As Range
    Dim i As Integer
    Dim j As Integer
    Dim lastRow As Integer

    ' Use WITH for shorthand (everything starting ".")
    ' because all cell references are to current sheet
    With ThisWorkbook.ActiveSheet

        ' Cycle through each cell in the used range of column 1,
        ' start by getting the last row
        lastRow = .UsedRange.Rows.Count

        ' Go bottom to top as inserting new rows pushes everything down
        For i = lastRow To 1 Step -1

            ' Set cell as row i, column 1
            Set vcell = .Cells(i, 1)

            ' If it is a cell with value LARGE then do some action
            If vcell.Value = "Large" Then

                ' loop for desired number of times, e.g. 3
                For j = 1 To 3

                    ' Insert a new row above VCELL
                    vcell.EntireRow.Insert

                Next j

            End If

        Next i

    End With

End Sub

To Run the code press F5 or click the green play button.

I've commented the code (all lines starting with an apostrophe) for explanation. This code cycles up through the column, and when a cell value is "Large", 3 rows are inserted. Of course in your example, change this to 10 and you can later change "Large" to anything you wish.

Below is the result:

Hope this helps

This is awesome, thanks so much! The looping is what always trips me up. Thank you so much!!! — Dylan Kindler, Dec 07 '16 at 02:59
No priblem. Please accept this as the answer if it did the job! — Wolfie, Dec 07 '16 at 08:40
@DylanKindler No problem. Please accept this as the answer if it did the job! — Wolfie, Dec 07 '16 at 08:42
@DylanKindler Click the tick (check mark) on left under the vote arrows — Wolfie, Dec 09 '16 at 08:58

Add multiple rows in Excel starting from same cell value VBA

1 Answers1