Solved: Insert Delete Rows Help.

[VISHAL120]

Hi all,

I am using the following codes to insert a standard line and delete selected rows.

[vba]Sub insert_Row()


Row_to_Insert = ActiveCell.Row
If Row_to_Insert <= Range("header_row").Row _
Then
MsgBox ("Cannot insert Row"), vbCritical, "Collar Analysis"
Exit Sub
End If

ActiveSheet.Unprotect
Rows(Row_to_Insert & ":" & Row_to_Insert).Insert
Range("Standard_Line").Copy
Rows(Row_to_Insert & ":" & Row_to_Insert).PasteSpecial xlPasteAll

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowDeletingRows:=True, AllowSorting:=True, _
AllowFiltering:=True
End Sub
Sub delete_row()

Dim ANS As String
Row_To_Delete = ActiveCell.Row

If Row_To_Delete <= Range("header_row").Row _
Then
MsgBox ("Cannot Delete row"), vbCritical, "Collar Anaysis"
Exit Sub
End If

ANS = MsgBox(" You sure to Delete row", vbQuestion + vbYesNo, "Collar Analysis")

If ANS = vbNo Then

Exit Sub


Else
ActiveSheet.Unprotect
Rows(Row_To_Delete & ":" & Row_To_Delete).Delete
' Range("Standard_Line").Copy
' Rows(Row_to_Delete & ":" & Row_to_Delete).PasteSpecial xlPasteAll

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowDeletingRows:=True, AllowSorting:=True, _
AllowFiltering:=True


End If
End Sub[/vba]
I want to modify it as follows :

1. for the insertion of the standard line : it shall insert according to the number of rows the user has defined not as actually if i have to insert 5 rows i have to click 5 times on the button.

2. For the deletion it shall delete selected rows that is if i select 3 rows at a time it shall delete the 3 rows at once. example if i select row 10 to row 14. it shall delete these rows at once.


Also please find attached a sample workbopok with the actual code.


thanks to guide me on that if possible.

[Bob Phillips]

[vba]
Sub insert_Row()
'Inserts the number of rows equivalent to the number selected
'e.g. select A5:A10 and 6 rows are inserted at A5

Row_to_Insert = ActiveCell.Row
Num_to_Insert = Selection.Rows.Count
If Row_to_Insert <= Range("header_row").Row _
Then
MsgBox ("Cannot insert Row"), vbCritical, "Collar Analysis"
Exit Sub
End If

ActiveSheet.Unprotect
Rows(Row_to_Insert).Resize(Num_to_Insert).Insert
Range("Standard_Line").Copy
Rows(Row_to_Insert).Resize(Num_to_Insert).PasteSpecial xlPasteAll

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowDeletingRows:=True, AllowSorting:=True, _
AllowFiltering:=True
End Sub


Sub delete_row()
'Deletes the number of rows equivalent to the number selected
'e.g. select A5:A10 and 6 rows are inserted at A5

Dim ANS As VbMsgBoxResult

Row_to_Delete = ActiveCell.Row
Num_to_Delete = Selection.Rows.Count
If Row_to_Delete <= Range("header_row").Row _
Then
MsgBox ("Cannot Delete row"), vbCritical, "Collar Anaysis"
Exit Sub
End If

ANS = MsgBox(" You sure to Delete row(s)", vbQuestion + vbYesNo, "Collar Analysis")

If ANS = vbYes Then

ActiveSheet.Unprotect

Rows(Row_to_Delete).Resize(Num_to_Delete).Delete
' Range("Standard_Line").Copy
' Rows(Row_to_Delete & ":" & Row_to_Delete).PasteSpecial xlPasteAll

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowDeletingRows:=True, AllowSorting:=True, _
AllowFiltering:=True
End If
End Sub[/vba]

[VISHAL120]

Hi Bob,

Thanks a lot for this quick reply. This Solved my problem now.

Thanks again.