EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Lookup & Reference Functions VBA Insert Row
Secondary Sidebar
VBA Lookup & Reference Functions
  • VBA Lookup & Reference
    • VBA Selecting Range
    • VBA Range
    • VBA Match
    • VBA Transpose
    • VBA Delete Row
    • VBA Range Cells
    • VBA Delete Column
    • VBA Union
    • VBA Insert Column
    • VBA Named Range
    • VBA Hyperlink
    • VBA Last Row
    • VBA Name
    • VBA OFFSET
    • VBA Hide Columns
    • VBA Selection Range
    • VBA Columns
    • VBA Insert Row
    • VBA LOOKUP
    • VBA VLOOKUP Function
    • VBA Resize
    • Compare Two Columns in Excel using VLOOKUP
    • VLOOKUP with Different Sheets

VBA Insert Row

By Pallavit SharmaPallavit Sharma

VBA Insert Row

Excel VBA Insert Row

As there are two things in this word one is VBA and other is Insert Row. In this, I’ll be explaining how to Insert Row in Excel using VBA (Visual Basic for Applications).

VBA – It’s a programming language for those who work in Excel and other Office programs, so one can automate tasks in Excel by writing so-called Macros. Using VBA coding we can perform all most all the tasks that we perform in Excel as we can copy, paste, delete, etc.

Insert Row – It is used to insert a row in Excel between another row with the help of automation i.e. on one click.

Suppose we are entering the Employee details in Excel and we have skipped some of the Employees and want to insert the details of the missing ones in between so we have to create a space and insert a row to enter their details. Instead of inserting row one by one we can automate the task of inserting rows using VBA and creating Macros.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

There are many ways using which we can insert the rows using VBA coding. The function used for inserting a row in VBA is:

Range().Row().EntireRow.Insert

Let’s consider we have a dataset which consists of columns like Customer Name, Product, Sales, Quantity, Shipping Cost, Order Priority.

VBA Insert Row Example 1-1

Suppose on the chance that we need to shift the columns down to insert a 3 new row between 3 and 4 to insert a new missing record. Instead of inserting row one by one we will automate this to insert all the 3 rows on a single click.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,328 ratings)

How to Insert Rows in Excel VBA?

Below are the different examples to insert rows in Excel using VBA code.

You can download this VBA Insert Row Excel Template here – VBA Insert Row Excel Template

Example #1 – Using Entire Row

Follow the below steps to insert Rows in excel using VBA code.

Step 1: To create a Macro we need to select the Developer Tab.

VBA Insert Row Example 1-2

Step 2: After the Developer tab opens, insert a button from the Controls group. Click on Insert and select the first option from ActiveX Controls. As you can see that Command Button.

VBA Insert Row Example 1-3

Step 3: Drag the arrow at any cell to create a Command Button.

VBA Insert Row Example 1-4

Step 4: To enter the Insert function Right-click on the Comand Button i.e. Insert 3 Rows and click on View Code.

View Code

Step 5: When you click on the View code, the following code appears.

Code:

Private Sub CommandButton1_Click()

End Sub

VBA Insert Row Example 1-6

Step 6: We have to write our code of INSERT ROW. As mentioned previously we have to use Rows.EntireRow function with RANGE in VBA. In this code, we have to insert 3 between 3rd and 4th.

Code:

Private Sub CommandButton1_Click()

Range("A3").rows("3:5").EntireRow.Insert

End Sub

VBA Insert Row Example 1-7

In the above line of code, A3 is a column where we have to insert row and Rows(3:5) is we have to insert 3 new rows.

Step 7: Run the code by clicking on the Insert 3 Rows Command Button.

Insert 3 Rows

Example #2 – Using Row Numbers

Similarly, we will write a code to insert 2 rows on a click of a button.

VBA Insert Row Example 2-1

In this, we need to mention row from start row to end row number using RANGE followed by INSERT which will insert the rows.

Code:

Private Sub CommandButton2_Click()

Range("3:4").Insert

End Sub

Using RANGE followed by INSERT

When we click on the button “Insert 2 Rows” we see that 2 rows have been inserted in between 3rd and 4th row.

Insert 2 Rows

Similarly, we can customize our function to insert as many rows as we can.

Example #3 – Using Active Cell

Active cell means the cell that is presently selected. We will use Active cell to insert rows. Assume we are on cell B3 and want to insert the cell above it we will use active cell property. We will use the following code for the active cell.

Code:

Private Sub CommandButton3_Click()

ActiveCell.EntireRow.Insert

End Sub

VBA Insert Row Example 3-1

When we click on the button “Active Cell Property” we see that one cell is inserted above the selected cell. In the above, we have selected B5 and below we can see that one row is inserted above it.

Active Cell Property

Example #4 – Using Active Cell with Offset

Suppose we want to insert a row after 3 rows of the active cell, for this we will use OFFSET function. Suppose we are in cell B4 and have to insert row after 2 rows, we will use this code.

Code:

Private Sub CommandButton4_Click()

ActiveCell.Offset(2, 0).EntireRow.Insert

End Sub

Using Active Cell with Offset Function

When we click on the button “Active Cell using Offset” one row is inserted in B6.

Active Cell using Offset

Things to Remember

  • It is used to insert rows in Excel by Automating it.
  • VBA Insert Row is used with RANGE object in VBA.
  • Before working do enable the “Enable all Macros” in Macro settings.

Recommended Articles

This is a guide to VBA Insert Row. Here we discuss how to insert Row in Excel Using VBA code along with practical examples and downloadable excel template. You may also look at the following articles to learn more –

  1. VBA GetObject
  2. VBA StrConv
  3. VBA AND
  4. VBA Get Cell Value
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more