Tuesday, December 30, 2014

How to insert rows or delete rows in a protected excel sheet

Hi,

While inserting row first you need to enter how many rows to be inserted & then select cell after which rows to be inserted..

For deleting row you will be prompted for selecting cell for which you need to delete rows.

'Macro for Inserting rows
Sub Insert_Row()
Dim n As Integer 'number of rows to insert
Dim rng As Range
n = Application.InputBox("How many rows you need to insert?", Type:=2)
Set rng = Application.InputBox("Select cell after which you want to insert row?", Type:=8)
ActiveSheet.Unprotect Password:="password"
rng.Select
rng.Resize(n, 1).EntireRow.Insert shift:=xlDown
ActiveSheet.Protect Password:="password"
End Sub
'Macro for Deleting rows
Sub Delete_Rows()
Dim rng As Range
ActiveSheet.Unprotect Password:="password"
Set rng = Application.InputBox("Select cell after which you want to insert row?", Type:=8)
rng.EntireRow.Delete
ActiveSheet.Protect Password:="password"
End Sub