delete row code to be modified very slightly??
 

~~~~~~~~~~~
Sub DeleteEvent()
If Not
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,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

delete row code to be modified very slightly??
 

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

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Corey" wrote in message
...
~~~~~~~~~~~
Sub DeleteEvent()
If Not
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,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

delete row code to be modified very slightly??
 

Just offset the row one like this:

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

John

"Corey" wrote:

~~~~~~~~~~~
Sub DeleteEvent()
If Not
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,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

delete row code to be modified very slightly??
 

Sub DeleteEvent()
If Not
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,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" wrote in message
...
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

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Corey" wrote in message
...
~~~~~~~~~~~
Sub DeleteEvent()
If Not
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,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

delete row code to be modified very slightly??
 

You must also change the range
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,A52,A56,A60,A64,.....................

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

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Corey" wrote in message
...
Sub DeleteEvent()
If Not
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,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" wrote in message
...
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

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Corey" wrote in message
...
~~~~~~~~~~~
Sub DeleteEvent()
If Not
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,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

delete row code to be modified very slightly??
 

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....
"Ron de Bruin" wrote in message
...
You must also change the range
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,A52,A56,A60,A64,.....................

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

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Corey" wrote in message
...
Sub DeleteEvent()
If Not
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,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" wrote in message
...
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

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Corey" wrote in message
...
~~~~~~~~~~~
Sub DeleteEvent()
If Not
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,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

delete row code to be modified very slightly??
 

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" wrote in message
...
You must also change the range
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,A52,A56,A60,A64,.....................

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

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Corey" wrote in message
...
Sub DeleteEvent()
If Not
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,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" wrote in message
...
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

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Corey" wrote in message
...
~~~~~~~~~~~
Sub DeleteEvent()
If Not
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,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

delete row code to be modified very slightly??
 

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


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Corey" wrote in message
...
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....
"Ron de Bruin" wrote in message
...
You must also change the range
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,A52,A56,A60,A64,.....................

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

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Corey" wrote in message
...
Sub DeleteEvent()
If Not
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,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" wrote in message
...
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

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Corey" wrote in message
...
~~~~~~~~~~~
Sub DeleteEvent()
If Not
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,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

delete row code to be modified very slightly??
 

See my reply for another way Corey


--

Regards Ron de Bruin
http://www.rondebruin.nl



"Corey" wrote in message
...
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" wrote in message
...
You must also change the range
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,A52,A56,A60,A64,.....................

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

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Corey" wrote in message
...
Sub DeleteEvent()
If Not
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,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" wrote in message
...
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

--

Regards Ron de Bruin
http://www.rondebruin.nl



"Corey" wrote in message
...
~~~~~~~~~~~
Sub DeleteEvent()
If Not
Application.Intersect(Range("A28,A32,A36,A40,A44,A 48,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