PDA

View Full Version : Allow end users to delete multiple rows within a range only - VBA



Jaspal
06-19-2013, 06:18 AM
My Macro underneath allows users to delete a row in a selected range only,if a row outside the selection is chosen it comes with an error mesasage.
I also want to allow end users to to be able to delete multiple intermittent/together rows as well only within that selection, so the error message will still stay if they select outside the range.

If I update my code from Actice Cell to Selection.EntireRow.Delete, I can still select a row outside the range in another section and delet deletes it?

Thank you in advance.

' Delete Macro
Call UnprotectSheet(ActiveSheet.Name)
Dim msg
msg = MsgBox("Are you sure you want to delete Rows?", vbYesNo)
If msg = vbNo Then Exit Sub
Dim r, s As Integer, activeRow As Integer
Firstrow = Selection.Row
Lastrow = Selection.Rows.Count + Firstrow - 1
'activeRow = ActiveCell.Row
r = Range("A6").Value
s = Range("A8").Value
If Firstrow >= r And Lastrow <= s Then
'If activeRow >= r And activeRow <= s Then
Selection.EntireRow.Delete
Else
MsgBox ("You cannot delete this row, you can only delete the rows between " & r & " and " & s)
End If
'Range(r & ":" & s).Select
'Selection.Locked = False
Call ProtectSheet(ActiveSheet.Name)

End Sub

mikerickson
06-19-2013, 07:50 AM
The posted code uses Selection as the bounds for acceptable deletion. So does this
Sub test()
Dim uiRange As Range

On Error Resume Next
Set uiRange = Application.InputBox("select the rows to delete", Type:=8)

With Application.Intersect(uiRange.EntireRow, Selection)
.EntireRow.Delete
End With

On Error GoTo 0
End Sub