VBA insert rows excel macro helps while automating and dealing with the records. For example, we may automate certain task based on the number of items in certain category. And the number of items may not be equal in all the situations it may vary time to time. We will see a practical example in this topic.

How to Insert Rows in Excel Worksheet using VBA – Solution(s):

We can insert use EntireRow.Insert method to insert rows. The following is the example code to inserting rows in excel worksheet.

VBA insert rows excel – An Example

The following example will show you how to insert a row in Excel Worksheet. You can insert multiple rows at a time.

Code:
Sub sbInsertingRows()
'Inserting a Row at at Row 2
Range("A2").EntireRow.Insert
'
'Inserting 3 Rows from 3
Rows("3:5").EntireRow.Insert
End Sub
Instructions:
  1. Open an excel workbook
  2. Press Alt+F11 to open VBA Editor
  3. Insert a Module for Insert Menu
  4. Copy the above code and Paste in the code window
  5. Save the file as macro enabled workbook
  6. Press F5 to run it
Input:

Shcreen-shot of example, before executing of the above code. You can see the 10 rows of data available in the worksheet.
Inserting Rows Examples 1

Output:

Shcreen-shot of example, after executing of the above code. You can see the 4 new rows are inserted in the worksheet.

Inserting Rows Examples 2

Inserting Rows in Worksheet using Excel VBA – Case study

The following example create the list if items by inserting the rows based on the numbers mentioned for each category.

Code:
Sub sbInsertingRowsCaseStudy()

Dim iCntr, jCntr

For iCntr = 2 To 4 ' for each category
    
    'Find the start row of category
    startRow = Application.WorksheetFunction.Match(Cells(iCntr, 1), Range("A16:A3300"), 0) + 15 'assuming maximum items are around3000
    For jCntr = 1 To Cells(iCntr, 2) 'print items
         Rows(startRow + 2).EntireRow.Insert
        Cells(startRow + 2, 2) = "Item " & Cells(iCntr, 2) - jCntr + 1
    Next
Next

End Sub
Instructions:

Download the example file and click on the ‘Create Category List’, it will create the categories based on the number mentioned for each category.
Inserting Rows Examples and Case study

Inserting Rows in Worksheet using Excel VBA – Download: Example File

You can download the example file and see example codes on Inserting Rows in Excel Worksheet.

ANALYSISTABS – Inserting Rows

Premium Project Management Templates

50+ Professional Project Management Templates!

A Powerful & Multi-purpose Templates for project management. Now seamlessly manage your projects, tasks, meetings, presentations, teams, customers, stakeholders and time. This page describes all the amazing new features and options that come with our premium templates.

PREMIUM TEMPLATES
LIMITED TIME OFFER
ON SALE80% OFF
BROWSE ALL TEMPLATES

50+ Project Management Templates Pack

Excel PowerPoint Word

VIEW DETAILS

Ultimate Project Management Template – Advanced

Excel Template

VIEW DETAILS

ULTIMATE PROJECT MANAGEMENT TEMPLATE

Excel Template

VIEW DETAILS

20+ Excel Project Management Pack

Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack

PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack

Word Templates

VIEW DETAILS

By Published On: March 28th, 2013Categories: Excel VBATags:

Share This Story, Choose Your Platform!

About the Author: Valli

Excel VBA Developer having around 8 years of experience in using Excel and VBA for automating the daily tasks, reports generation and dashboards preparation. Valli is sharing to helps us automating daily tasks.

43 Comments

  1. venkat January 15, 2015 at 4:06 PM

    I need small information .In excel every 27 rows after insert 5 rows.how to do this .Please suggest to me

    • PNRao January 16, 2015 at 2:47 PM

      Hi Venkat,

      Here the VBA macro to insert n umber of rows after every nth row:

      Sub Insert_Rows_After_Every_Nth_Row()
      lRow = 41 ' last row in your sheet
      'If yoyr data is not fixed:
      'please refere the 100+ useful macro to find last row macro
      
      
      everyNthRows = 27 'After every nth row
      NumRowsTobeInserted = 5 'Number of rows to be inserted
      
      
      
      Do While lRow >= everyNthRows
      If lRow Mod everyNthRows = 0 Then Rows(lRow + 1 & ":" & lRow + NumRowsTobeInserted).Insert
      lRow = lRow - 1
      Loop
      
      End Sub
      

      Hope this helps!
      Thanks-PNRao!