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

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

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?

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