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

Hey! Join Our Community

Get Quick Responses & Experts' Answers in Minutes!
Get Notified - When Answered Your Question!