Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting specific information
Hi Group
I have created this code (its just a subset of e longer code) on the Worksheet_Change event: If Not Intersect(Target, Range("d4:d9")) Is Nothing Then Select Case Target.Value Case Is = 1 Range("M13") = Target.Offset(0, -1) Case Is = 2 Range("M20") = Target.Offset(0, -1) Case Is = 3 Range("H9") = Target.Offset(0, -1) Case Is = 4 Range("H17") = Target.Offset(0, -1) Case Is = 5 Range("H25") = Target.Offset(0, -1) Case Is = 6 Range("H33") = Target.Offset(0, -1) End Select End If It makes it possible to type numbers in D4:D9. Depending on what number is typed, the content of the corresponding row in column C is copied to a new destination. This Works perfectly all right. Now the guy will be using it, would like to add a delete function. If he deletes a number in one of the cells, D4 to D9, he wants the corresponding info in column M and H to be deleted as well. I can't see an easy way to do this, without storing whats already in the cell, before deletion. Like if the number 2 is deleted, then M20 has to be deleted too. But when the cell content is deleted, I won't know, what was in it before. Do anyone have an idea on how to accomplish this? Jan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting specific information
Addition:
And if all the cells in D4:D9 are selected and deleted at once, of course all the relevant information in H and M must be deleted as well. Jan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting specific information
wrote in message
... Hi Group I have created this code (its just a subset of e longer code) on the Worksheet_Change event: If Not Intersect(Target, Range("d4:d9")) Is Nothing Then Select Case Target.Value Case Is = 1 Range("M13") = Target.Offset(0, -1) Case Is = 2 Range("M20") = Target.Offset(0, -1) Case Is = 3 Range("H9") = Target.Offset(0, -1) Case Is = 4 Range("H17") = Target.Offset(0, -1) Case Is = 5 Range("H25") = Target.Offset(0, -1) Case Is = 6 Range("H33") = Target.Offset(0, -1) End Select End If It makes it possible to type numbers in D4:D9. Depending on what number is typed, the content of the corresponding row in column C is copied to a new destination. This Works perfectly all right. Now the guy will be using it, would like to add a delete function. If he deletes a number in one of the cells, D4 to D9, he wants the corresponding info in column M and H to be deleted as well. I can't see an easy way to do this, without storing whats already in the cell, before deletion. Like if the number 2 is deleted, then M20 has to be deleted too. But when the cell content is deleted, I won't know, what was in it before. Do anyone have an idea on how to accomplish this? Jan You could mirror the values on another (hidden) sheet? Then youd know what was deleted by checking the other sheet. Unless Ive misunderstood? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting specific information
Hi Jan,
Am Fri, 16 May 2014 21:09:03 +0100 schrieb IanKR: I can't see an easy way to do this, without storing whats already in the cell, before deletion. Like if the number 2 is deleted, then M20 has to be deleted too. But when the cell content is deleted, I won't know, what was in it before. try: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("D4:D9")) Is Nothing _ Then Exit Sub Dim myStr As String Dim myArr As Variant Dim i As Long myStr = "M13,M20,H9,H17,H25,H33" myArr = Split(myStr, ",") Select Case Target.Value Case 1 To 6 Range(myArr(Target.Value - 1)) = Target.Offset(, -1) End Select For i = 1 To 6 If WorksheetFunction.CountIf(Range("D4:D9"), i) = 0 Then Range(myArr(i - 1)).ClearContents End If Next End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
add data without deleting information in cell | Excel Discussion (Misc queries) | |||
Deleting specific rows with a specific criteria using inputbox | Excel Programming | |||
Deleting information in cells | Excel Worksheet Functions | |||
deleting information from a cell | Excel Discussion (Misc queries) | |||
Keep value after deleting original information | Excel Programming |