Macro that deletes rows only

[Limone]

Hi.

I have a code that allows the end user to delete selected rows from a protected table. The problem is, as it is now my macro can delete pretty much anything that gets selected and I'm worried that something else outside the table will eventually get deleted by accident, even though I've got an alert message you have to agree to first and everything.

Could someone help me complete my code so that only rows types of objects can get deleted, possibly only if within a specified named range?

Thanks in advance.

Sub Delete_Click()
'
' Delete_Click Macro
'


'
Application.ScreenUpdating = False
ActiveSheet.Unprotect
Dim answer As Integer
[INDENT]answer = MsgBox("Are you sure you want the selected object to be deleted?", vbYesNo + vbQuestion, "Delete rows")[/INDENT]
[INDENT]If answer = vbYes Then[/INDENT]
[INDENT=2]Selection.Delete Shift:=xlUp[/INDENT]
[INDENT=2]Else if[/INDENT]
[INDENT=3]'do nothing[/INDENT]
[INDENT]End If[/INDENT]
ActiveSheet.Protect
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Does this code do what you want...

[table="width: 500"]
[tr]
	[td]Sub Delete_Click()
  '
  ' Delete_Click Macro
  '
  Dim Answer As Long
  ActiveSheet.Unprotect
  Answer = MsgBox("Are you sure you want the selected object to be deleted?", vbYesNo + vbQuestion, "Delete rows")
  If Answer = vbYes And TypeName(Selection) = "Range" Then
    If Selection.Columns.Count = Columns.Count Then
      Selection.Delete Shift:=xlUp
    Else
      MsgBox "Wait a minute! You can only delete entire rows and you have selected something other than an entire row or rows! Your action has been cancelled!", vbCritical
    End If
  End If
  ActiveSheet.Protect
End Sub[/td]
[/tr]
[/table]

 

[Limone]

Hi Rick, I've tried your code out. Unfortunately, even though it does ask me for confirmation and no error message comes up, nothing happens after I say yes (that is, even when I try to delete something that's not a row).
I've also changed the "columns" bit to "row" in case that was the problem but it seems to make no difference.

 

[Rick Rothstein]

Hi Rick, I've tried your code out. Unfortunately, even though it does ask me for confirmation and no error message comes up, nothing happens after I say yes (that is, even when I try to delete something that's not a row).
I've also changed the "columns" bit to "row" in case that was the problem but it seems to make no difference.

I may have misunderstood what you were looking for... I thought you wanted to allow only entire rows to be deleted. Can you clarify exactly what functionality you want to allow?

 

[Limone]

Ok, I got what the problem was. It's not possible to stay within the named range while doing what I want to do and that's why it wasn't deleting things.
Thanks a lot for your help!