Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting values less than 100 | Excel Worksheet Functions | |||
List deleting or changing | New Users to Excel | |||
Deleting or changing a range name | Excel Discussion (Misc queries) | |||
deleting values in a worksheet without deleting the formulas | Excel Worksheet Functions | |||
Deleting/Adding/Changing values based on ComboBox1 Value | Excel Programming |