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
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
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
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
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.
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