How To Insert Row In Excel Vba

How to Insert a Row in Excel VBA

In Excel VBA, you can use the “Rows” property of a worksheet object to insert a new row. The Steps below explain how to do this.

Step 1: Access the Worksheet

First, you need to access the worksheet where you want to insert a row. This can be done by using the “Worksheets” collection and specifying the worksheet name or index.

    
      Sub InsertRow()
          Dim ws As Worksheet
          Set ws = Worksheets("Sheet1") ' Replace "Sheet1" with the actual sheet name
    
  

Step 2: Specify the Row

Next, you need to specify the row where you want to insert a new row. This can be done by using the “Rows” property of the worksheet and specifying the row number.

    
      Dim rowNumber As Integer
      rowNumber = 5 ' Specify the row number where you want to insert a new row
    
  

Step 3: Insert the Row

Finally, you can use the “Insert” method of the Rows object to insert a new row at the specified position.

    
      ws.Rows(rowNumber).Insert ' Inserts a new row at the specified row number
    
  

Example

Let’s say you want to insert a new row at row number 5 in “Sheet1”. You can use the following VBA code:

    
      Sub InsertRow()
          Dim ws As Worksheet
          Set ws = Worksheets("Sheet1")
          
          Dim rowNumber As Integer
          rowNumber = 5
          
          ws.Rows(rowNumber).Insert
      End Sub
    
  

This code will insert a new row at row number 5 in “Sheet1”.