In this tutorial, we will look at how to insert a row or a column using a VBA code in Excel. We will also explore what are the different ways to write a macro for this.
Insert a Single Row using VBA
To insert a row using a VBA code, you need to use the “Entire Row” property with the “Insert” method. With the entire row property, you can refer to the entire row using a cell and then insert a new row there. By default, it will insert a single row before the cell that you have mentioned.
- First, specify a cell using the range object.
- Now, enter a dot (.) to get the list of properties and methods.
- After that, select the “Entire Row” property or type it.
- In the end, again enter a dot (.) and select the “Insert” method or type it.
Your code is ready here to insert a row. Now when you run this code, it will instantly insert a new row before cell A1.
Insert Multiple Rows
There are two ways to insert multiple rows in a worksheet that I have found. The first is the same insert method that we have used in the above example.
With this, you need to specify a range whose count is equivalent to the count of rows you want to insert. Now let’s say you want to insert 5 rows after, in that case, you can use a code like the following.
To be honest, I haven’t found this method quite useful because you need to change the range if you want to change the count of the rows.
So here’s the second method.
Dim iRow As Long Dim iCount As Long Dim i As Long iCount = InputBox(Prompt:="How many rows you want to add?") iRow = InputBox _ (Prompt:="After which row you want to add new rows? (Enter the row number") For i = 1 To iCount Rows(iRow).EntireRow.Insert Next i
When you run this code, it asks you to enter the number of rows that you want to add and then the row number where you want to add all those rows. It uses a FOR LOOP (For Next) to loop that number of times and insert rows one by one.
Insert Rows Based on the Cell Values
If you want to insert rows based on a cell value, then you can use the following code.
Dim iRow As Long Dim iCount As Long Dim i As Long iCount = Range("A1").Value iRow = Range("B1").Value For i = 1 To iCount Rows(iRow).EntireRow.Insert Next i
When you run this macro, it takes the count of rows from cell A1 and the row where you want to add rows from cell B1.
Insert a Row without Formatting
When you insert a row where the above row has some specific formatting, in that case, the row will also have that formatting automatically. And the simplest way to deal with this thing is to use clear formats. Consider the following code.
When you run the above code, it inserts a new row before the 7th row. Now, what happens, when you insert a row before the 7th row that new row becomes the 7th row, and then the second line of code clears the formats from that row.
Insert Copied Row
You can also use the same method to copy a row and then insert it somewhere else. See the following code.
Application.CutCopyMode = False With Worksheets("Data") .Rows(5).Copy .Rows(9).Insert Shift:=xlShiftDown End With Application.CutCopyMode = True
More on VBA Range and Cells
- How to Set (Get and Change) Cell Value using a VBA Code
- How to Sort a Range using VBA in Excel
- How to Create a Named Range using VBA (Static + Dynamic) in Excel
- How to Merge and Unmerge Cells in Excel using a VBA Code
- How to Check IF a Cell is Empty using VBA in Excel
- VBA ClearContents (from a Cell, Range, or Entire Worksheet)
- Excel VBA Font (Color, Size, Type, and Bold)
- How to AutoFit (Rows, Column, or the Entire Worksheet) using VBA
- How to use OFFSET Property with the Range Object or a Cell in VBA
- VBA Wrap Text (Cell, Range, and Entire Worksheet)
- How to Copy a Cell\Range to Another Sheet using VBA
- How to use Range/Cell as a Variable in VBA in Excel
- How to Find Last Rows, Column, and Cell using VBA in Excel
- How to use ActiveCell in VBA in Excel
- How to use Special Cell Method in VBA in Excel
- How to Apply Borders on a Cell using VBA in Excel
- How to Refer to the UsedRange using VBA in Excel
- How to Change Row Height/Column Width using VBA in Excel
- How to Select All the Cells in a Worksheet using a VBA Code
- How to Insert a Column using VBA in Excel