VISHAL120
04-04-2012, 11:04 PM
Hi all,
I am using the following codes to insert a standard line and delete selected rows.
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
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.
:bug::banghead:
I am using the following codes to insert a standard line and delete selected rows.
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
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.
:bug::banghead: