Worksheet Change Method
 

in the appropriate sheet
Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Address = "$A$21" Then
Rows(Target + 1 & ":15").Select
Selection.EntireRow.Delete
End If
End Sub

but this will move the value in cell A20 up into the data range (use A21 if
your user presses enter to move to next cell down

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Address = "$A$21" Then
Rows(Target + 1 & ":15").EntireRow.Select
Selection.ClearContents
Range("a20").Select
End If

End Sub
is a better bet

Steve


"Michael" wrote in message
...

Hello all,

Is is possible to maniputlate the Worksheet_Change_Method
to alter the struture of a range of cells ?. I have a
range of cells containing data say range(A1:G15).

I have a variable cell (A20) that is adjustable by the
user by entering a value from 1-15.

Now if the user enters a value of say 8 IN (A20), I would
like to delete rows(A9-A15)in the range (A1:G15), and if
it is 4, then rows (A5-A15)etc.

I hope that makes some sense or is it even possible ?

I have looked through the archives for deleting and
inserting cells, but cannot find what I am looking for.

Any help would be appreciated,

Thanks,

Michael