Deleting specific information

[jan120253]

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 D49. 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

 

[jan120253]

Addition:

And if all the cells in D49 are selected and deleted at once, of course all the relevant information in H and M must be deleted as well.

Jan

 

[IanKR]

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 D49. 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 you’d know what
was deleted by checking the other sheet. Unless I’ve misunderstood?

 

[Claus Busch]

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("D49")) 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("D49"), i) = 0 Then
Range(myArr(i - 1)).ClearContents
End If
Next
End Sub


Regards
Claus B.