delete row code to be modified very slightly??

C

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
 
R

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
 
J

John Bundy

Just offset the row one like this:

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

John
 
C

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....
 
R

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........................
 
C

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....
 
C

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....
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top