Any Formula For Automatic Deletion Of Entry?

[Dargio]

Hey All,

First of all I'm sorry if this is the wrong thread for posting my question. But I appreciate any help that can be given. I have an Excel sheet consisting ID number in cell B3:B31. Is there any formula or macros or anything that can be included so that, when I key in a particular ID maybe 34069, in any of the cells for example, B2, B6:B20 or maybe F3:F6, the same ID will be automatically deleted in within the cells of B3:B31. I've attached the file. Any help is greatly appreciated as this can help me a lot in my work of handling a near 1000 ID. Please help me.

Thank You to All.

[arlu1201]

You say that the ID will be entered in either B2, B6:B20 or F3:F6 and it should be deleted from B3:B31. But you will be entering the number in B6:B20 and u want it to be deleted again?

Please explain clearly.

[Dargio]

ok sorry. what i meant was, if i key in any ID that is stated in within I3:I31 (my apologies for the wrong cell), into any of the other cells like B2 OR B6:B20 OR F3:F6, i want that particular ID to be automatically deleted from I3:I31. once again i apologise.

[arlu1201]

Right click on the sheet where you need the code to work and select "View Code". Put this code in

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim delid As Long
Dim delcell As Variant

    If Not Intersect(Target, Range("$B$2,$B$6:$B$20,$F$3:$F$6")) Is Nothing Then
        delid = Target.Value
        Set delcell = Columns("I:I").Find(what:=delid, after:=Range("I1"), LookIn:=xlFormulas)
        If Not delcell Is Nothing Then
            delcell.Delete
        End If
    End If
End Sub

[Dargio]

erm.... another question, how do i put the code??? i'm a noob.... does it works with 97 excel and 2010?

[arlu1201]

I am not sure about excel 97 (i learnt VBA only after excel 2003 came into use ). But it should work in 2010.

Copy the code above, right click on your worksheet, select view code from the menu. Copy paste the code into the blank screen on the right.

[Dargio]

k i tried. it works. sorry as i'm a bit too noob for this. can the IDs in one of the cells of I3:I31, be cleared instead of deleted like now?

[arlu1201]

Sure, just change

 delcell.Delete

to

 delcell.clear

[Dargio]

I am really sorry to bug u with my questions again n again. clear works fine, but is it possible just to delete entry and let the colored cells stay instead of clearing the whole cell to stock? i am really sorry again....

[arlu1201]

Then try replacing .clear with .clearcontents

[Dargio]

yay!!! it works!!!!! thanks alot arlette!!! i appreciate it alot!!!!!