Canceling items from a sheet

Hi guys and gals,

Need your help. In sheet one, down column A, I have a list of numbers 1-100. What I want to do is have those numbers be removed from sheet 1 when entered in column A in sheet two. For example, in sheet 2, A1, number 53 is entered. I would like number 53 removed on sheet 1. Any thoughts?

Thanks in advance.

Skiman

Skiman,

Not sure how many records you're talking about (the following will probably make data entry cumbersome on large datasets), but let me know if the following does the trick (note that it must be copied into the Worksheet_Change event for Sheet2):

Please Login or Register  to view this content.

HTH

Robert

Thanks for taking the time, Robert. I'm wondering...where exactly does the listed code need to be copied into? Sorry for the confusion.

Skiman

Hi Skiman,

Sorry I should included the following four steps:

1. Copy (Ctrl + C) my code
2. Right click on the Sheet2 tab name and from the shortcut menu, click View Code
3. Paste (Ctrl + V) the code copied from Step 1
4. From the File menu select Close and Return to Microsoft Excel

Let me know if you have any other queries and/or how it goes.

Kind regards,

Robert

Thanks Robert. I will be testing this with my data over the next 24hrs. Thanks again for the time. I will reply if I have any problems.

Thanks again. Much appreciated.

Skiman.

Robert,

You are a life saver. Works great. I have a questions though. Is there a way to expand the field in sheet 2 or sheet 1? What I mean is, is there a wey to allow items to be typed in fields other than column A? Is there a way to easly expand a command in the code to allow something typed in F27 in sheet 2, for example, to also be removed from Sheet 1? Robert, this thing is great. If it could be expanded it could help even more.

Thanks again.

Skiman.

Hi Skiman,

You'll have to manually change the following lines of code to reflect which column(s) you need to manipulate:

Firstly, change the following A:A (bolded) to automatically execute the code whenever a change is made in a particualr column(s). For example, if you change this to "A:F" then any change made in these columns will start the code.

Please Login or Register  to view this content.

Next, change the following A1:A (bolded) to indicate what range the matching item from Sheet1 is to be deleted from.

Please Login or Register  to view this content.

Lastly, you may or may not have to change the A (bolded) in following depending if Column A is a reliable representation of the last cell in Sheet1:

Please Login or Register  to view this content.

HTH

Robert

Robert,

I've got one last thing for you. Is there a way to copy a group of numbers from another excel file and copy/paste them into sheet 2 (with your code in there) and then have those numbers that were pasted be removed from sheet 1? I tried doing it kept giving me a "type mismatch". When I go to Debug it keeps pointing to this line:

If cell.Value = lngMyValue Then

I was trying to find a better way to enter the numbers instead of one by one. You sort of eluded to it in your first post(that it may be cumbersome).

Any suggestions? You can reply with a mailing address for me to send the check. LOL!

Thanks again.

Skiman

Hi Skiman,

Data entry may be slowed on large datasets (i.e. thousands of records) as for every change it checks if the value exists in Sheet1 and if does, deletes the row where the value resides. If this isn't the case, I wouldn't worry.

The error below may be due to the code trying to assign a non numeric value to the lngMyValue variable.

The code could be adapted so that it could run on an existing data set that has been copied and pasted into Sheet2, but I'd say you'd have to start a new thread as it's quite different to your original one.

HTH

Robert

Thanks Robert.