Lesson#91: Insert rows in worksheet using VBA

Lesson#91: Insert rows in worksheet using VBA

In this post we shall take a VBA lesson which is how to insert rows in worksheet using VBA. So for this you have to open an excel worksheet and press Alt+F11 to go to VBA editor.

For inserting a row at a particular range say A5  you have to write a simple code.

Sub insertrow()
Range(“A5”).EntireRow.Insert
End Sub
If you want to insert more than one row at a range say A5:A10 you have to change the code slightly.
Sub insertrow()
Range(“A5:A10”).EntireRow.Insert
End Sub
If you want to insert row at the row where you have clicked right now.
Sub insertrow()
ActiveCell.EntireRow.Insert
End Sub

Then I will insert a button in the worksheet and clicking on that an input box will ask how many rows to insert after where I have clicked right now. Go to Developer Tab>Insert>Button then click on New in Assign Macro window and paste the below code.

Sub Button1_Click()
Call insertrow
End Sub
Sub insertrow()
Dim num As Integer
num = InputBox(“How many rows to insert?”)
Range(ActiveCell, ActiveCell.Offset(num – 1, 0)).EntireRow.Insert
End Sub
That’s how to insert a row in worksheet with VBA.
See also  Lesson#182: Color all empty cells in red in a selected range with VBA

Hi! I am Puspendu. I am the founder and author of Excelabcd. I am little creative person, blogger and Excel-maniac guy. I hope you enjoy my blog.

Leave a Reply

Your email address will not be published. Required fields are marked *

*