macro to delete multiple rows

T

Tonso

I have recorded, then modified, the following macro, which unprotects the
sheet, aks if you are sure you want to delete the row, deletes the current
row, copies down a cell in column A that numbers the row, and protects the
sheet. Works fine, but if the user wants to delete not only the current row,
but multiple rows below it also, the macro must be repeated. How can I
accomplish deleting multiple rows, as indicated by the user? For example,
rows 18 thru 20, or 29 thru 33, etc, or of course, just the current row,
with the message box prompting the user for the number of rows to delete.

Thanks

ActiveSheet.Unprotect Password:="slikto"
Answer = MsgBox("Are you sure you want to delete?", vbYesNo)
If Answer <> vbYes Then Exit Sub
Application.ScreenUpdating = False
ActiveCell.Rows("1:1") .EntireRow.Select
Selection.Delete Shift:=xlUp
ActiveCell.Select
Selection.Filldown
Application.ScreenUpdating = True
ActiveSheet.Protect Password:="slikto"
End Sub
 
D

Don Guillett

something along the lines of
x=inputbox("Enter rows")
cells(activecell.row,"a").resize(x,1).entirerow.delete
 
G

Gord Dibben

Set srng = Application.InputBox(prompt:= _
"Select Rows to Delete", Type:=8)
srng.EntireRow.Delete


Gord Dibben Excel MVP
 
A

AlfD

Hi!

Thank you for that!

Is it worth mentioning that it works with discontiguous selections
too?

Al
 
T

Tonso

I tried both methods. I created a new module and typed in both
and I obviously have done something wrong...
I get a "Compile error: Variable not defined" message for each.
the 1st macro stops at "srng ="
the 2nd macro stops at "x ="
I tried "Help" but could not figure it out. What am I doing wrong?
Sub DelRow4()
ActiveSheet.Unprotect Password:="slikto"
Application.ScreenUpdating = False
Set srng = Application.InputBox(prompt:="Select Rows to Delete",
Type:=8)
srng.EntireRow.Delete
Range("A" & ActiveCell.Row).Select
Selection.FillDown
Application.ScreenUpdating = False
ActiveSheet.Protect Password = "slikto"
End Sub

Sub delrow5()
ActiveSheet.Unprotect Password:="slikto"
Application.ScreenUpdating = False
x = InputBox("Enter Rows")
Cells(ActiveCell.Row, "a").Resize(x, 1).EntireRow.Delete
Range("A" & ActiveCell.Row).Select
Selection.FillDown
Application.ScreenUpdating = False
ActiveSheet.Protect Password = "slikto"



End Sub
 
D

Don Guillett

I just retested mine and it worked fine. I'm not sure about your copy down.
Sub delrow5()
' ActiveSheet.Unprotect Password:="slikto"
' Application.ScreenUpdating = False
'==============
'The part that I sent works just fine. If you say 2 it deletes 2 rows.
x = InputBox("Enter Rows")
Cells(ActiveCell.Row, "a").Resize(x, 1).EntireRow.Delete
'================
' Range("A" & ActiveCell.Row).Select
' Selection.FillDown

' Application.ScreenUpdating = False
' ActiveSheet.Protect Password = "slikto"
End Sub
 
Top