Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
add data without deleting information in cell Hobher Excel Discussion (Misc queries) 1 April 1st 09 02:01 PM
Deleting specific rows with a specific criteria using inputbox Greg Excel Programming 2 April 10th 08 04:31 PM
Deleting information in cells Ashley Excel Worksheet Functions 2 September 6th 07 07:06 PM
deleting information from a cell Katti1215 Excel Discussion (Misc queries) 1 January 25th 07 09:46 PM
Keep value after deleting original information crs Excel Programming 2 January 9th 07 06:24 AM


All times are GMT +1. The time now is 10:55 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"