Deleting/Changing values
 

Hi,

Im trying to tweak the code so that if i delete/clear a value, the row below
is deleted, thks a lots.
Also, currently If I change values a row is inserted, I would like it so that
the no of rows remain.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit:
With Target
If .Column = 1 Then
If .Cells.Count = 1 Then
.Offset(1).EntireRow.Insert xlShiftDown
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

Deleting/Changing values
 

The line:
..Offset(1).EntireRow.Insert xlShiftDown
inserts a row immediately below the Target row.
You say that you want to delete the row immediately below the target
row. Use:
..Offset(1).EntireRow.Delete

You also say you want the number of rows to remain constant. Obviously, you
cannot delete or insert a row and have the number of rows remain constant.
So I don't know what you want there.

You also say that you want this to happen if you clear or delete the
contents of a target cell. What you have will trigger the action if the
value of a target cell changes to anything, including blank. Add a line at
the beginning of the macro as:
If Target<"" Then Exit Sub. HTH Otto
"teresa" wrote in message
...
Hi,

Im trying to tweak the code so that if i delete/clear a value, the row
below
is deleted, thks a lots.
Also, currently If I change values a row is inserted, I would like it so
that
the no of rows remain.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit:
With Target
If .Column = 1 Then
If .Cells.Count = 1 Then
.Offset(1).EntireRow.Insert xlShiftDown
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

Deleting/Changing values
 

Teresa
In perusing the newsgroup I found where you posted numerous queries on
more or less the same subject. You should keep subsequent postings on the
same subject in the original thread. This makes it much simpler for someone
to help you because they can see the history of your questions and the
responses.
If you still have a problem with this subject matter, and if you wish,
send me a small file of what you have and an explanation of what you want to
happen when you do what you do. I'll work with you in resolving this issue
and get you something you can use. Send me the file direct. Please do not
attach a file to a newsgroup posting. My email address is
. Remove "cobia97" from this address. HTH
Otto
"teresa" wrote in message
...
Hi,

Im trying to tweak the code so that if i delete/clear a value, the row
below
is deleted, thks a lots.
Also, currently If I change values a row is inserted, I would like it so
that
the no of rows remain.


Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ws_exit:
With Target
If .Column = 1 Then
If .Cells.Count = 1 Then
.Offset(1).EntireRow.Insert xlShiftDown
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub