delete row code to be modified very slightly??

[Corey]

~~~~~~~~~~~
Sub DeleteEvent()
If Not
Application.Intersect(Range("A28,A32,A36,A40,A44,A48,A52,A56,A60,A64,A68,A72,A76,A80,A84,A88,A92,A96,A90"),
ActiveCell) Is Nothing Then
ActiveCell.Resize(4).EntireRow.Delete
End If
End Sub
~~~~~~~~~~~

The above code works great for my needs as it deletes the highlighted row
and the 3 rows below it also(4 in total).
But as i have now found once i protect the sheet i need to not allow that
row being selected anymore,
but instead i need the code to work if a cell is selected, rather than the
row. BUT the cell will be 1 row lower than the needed 1st row to be deleted.

EG.

Previously i would highlight row 28, and when i click a commandbutton to run
the code,
rows 28-31(4 rows) would be deleted.
Now that the sheet is protected the user cannot select the row to highlight
the 1st row to be deleted.
I now need the cell A29 to be selected(which is not locked) then when the
user runs the code from the commandbutton,
the above row and the folowing 3 rows(4 in total) will be deleted.


The rows can be anywhere in the sheet from 28-240(in lots of 4 rows when
deleted)

Can some help me to adjust this code to suit that?

ctm

 

[Ron de Bruin]

Hi Corey

Change the cells in the range to the unlocked cells and use this in the code

ActiveCell.Offset(-1, 0).Resize(4)

With offset we go one row up before we resize

 

[Guest]

Just offset the row one like this:

ActiveCell.Offset(-1, 1).Resize(4).EntireRow.Delete

John

 

[Corey]

Sub DeleteEvent()
If Not
Application.Intersect(Range("A28,A32,A36,A40,A44,A48,A52,A56,A60,A64,A68,A72,A76,A80,A84,A88,A92,A96,A90"),
ActiveCell) Is Nothing Then
' ActiveCell.Resize(4).EntireRow.Delete
ActiveCell.Offset(-1, 0).Resize(4).EntireRow.Delete ' <======= Is
that want you meant Ron ??

End If
End Sub

Ithe code does not seem to do anything now??

Corey....

 

[Ron de Bruin]

You must also change the range

Application.Intersect(Range("A28,A32,A36,A40,A44,A48,A52,A56,A60,A64,.....................

A28 must be A29 now and A32 must be ? and........................

 

[Corey]

After changing the ranges to suit,
I now get an error:
Delete method of range class failed.

And the :
ActiveCell.Offset(-1, 0).Resize(4).EntireRow.Delete
Is highlighted in the code??


Corey....

 

[Corey]

Never mind.
I had a called code to unprotect the sheet but i selected the wrong code
that was unprotecting the wrong shet.

Thanks for your assistance

Corey....

 

[Ron de Bruin]

If you protect your sheet manual it is not possible to delete rows with
locked cells in it in 2002 and up
if you check the delete rows setting when you protect your sheet.

Your only way is to protect your sheet with code
Remove your sheet protection of "Sheet1" ?
Insert this in the thisworkbook module

Private Sub Workbook_Open()
With Worksheets("sheet1")
.EnableSelection = xlUnlockedCells
.Protect Password:="hi", userinterfaceonly:=True
End With
End Sub

Then save/close and reopen the file

 

[Ron de Bruin]

See my reply for another way Corey