Macro to delete item if not found on a list

[hunsnowboarder]

Hi there Everyone!
Please try to help me if you can with my problem.
You can find attached the workbook I am working on.
In my workbook I have four sheets. Only two sheets are relevant in this case: "Sheet1" and "List". On "Sheet1" there in column A there is big range of codenumbers (highlighted with red).
On the "List" worksheet I have a smaller list (highlighted with green).
I have a code also in module1 but the code is not working.

I would like my macro to do the following. Check the codenumber from the "List" worksheet (green) and search for it in "Sheet1" column A (red). If this codnumber can be found in column A then leave the number on the green list. If it cannot be found in column A then, delete it from the green list. If you open the file, you can see on the "List" worksheet that there are three codenumbers in the green range. The upper and the bottom code number can be found on the "Sheet1" column A (red) range, but the middle number cannot be found. So if the macro would work correctly then it would delete the middle number, and leave the other two untouched! I hope you could understand my problem!
Thank you in advance for your help!

[DonkeyOte]

X-Post

http://www.mrexcel.com/forum/showthread.php?t=366700

[hunsnowboarder]

Is this problem really so hard? No one can help me?

[stanleydgromjr]

hunsnowboarder,

Here you go.

The number you were trying to clear "223587" in sheet "List", is in "Sheet1".

See the attached workbook "Delete item if not found on a list - hunsnowboarder - SDG.xls" in the zipped file "hunsnowboarder.Zip".

And, run the "Test" macro.


Have a great day,
Stan

[stanleydgromjr]

hunsnowboarder,

The data in your attached workbook looks familiar.

I was working on on a solution (then I accidently deleted the workbook), but, I think it was on another site.

I think you were looking to search for a number entered on Sheet "List", next to the "Search" cell, cell B3.

If the above is true, and you have not found a solution, can you post the workbook and the instructions again?


Have a great day,
Stan

[hunsnowboarder]

Dear Stan! First of all thanks a bunch for your very precise and accurate work! The macro is working brilliant. However there is a big issue... This macro works only for one sheet. In my case (as the real file I am working with contains around 160.000 records) the column A is spread into many (actually so far 3, but the list is growing) sheets. So I would like to have a macro which actually somehow unites the column A of all sheets, and does the search there. I hope you can understand what I mean.

I realized that my macro (which is working for one sheet as well) is also a wrong solution, because allways wants to find the code number from my list on the sheet that it is actually looking. I need a solution where the macro looks for the code number, and if it finds it on a sheet (it does not matter which one) will stop searching further and will jump to the next code number.

I really hope you can understand my problem!
And thanx again for that nice file, you have sent me!

[stanleydgromjr]

hunsnowboarder,

Thanks.

This macro works only for one sheet. In my case (as the real file I am working with contains around 160.000 records) the column A is spread into many (actually so far 3, but the list is growing) sheets. So I would like to have a macro which actually somehow unites the column A of all sheets, and does the search there. I hope you can understand what I mean.

In order to resolve your request, please attach another workbook with data in multiple columns, in sheets "Sheet1", "Sheet2", and "Sheet3".


Have a great day,
Stan

[hunsnowboarder]

Hi Stan!
I will post the required file as soon as I get home. (But you can also input some codenumbers on sheet 2 in column A and then try the macro if it works.)

Later I'll post the file. Thank you!

[hunsnowboarder]

As I promissed here is the file attached. Please help if you can as I am getting really crazy about this...

[Leith Ross]

Hello Hun,

I added the following macro to Module1. The 3 numbers you have on the "List' sheet all are present in column"A" of "Sheet1". This macro works and will find non matches and delete the entire row.

Please Login or Register  to view this content.

[stanleydgromjr]

hunsnowboarder,

[quoteThis macro works only for one sheet. In my case (as the real file I am working with contains around 160.000 records) the column A is spread into many (actually so far 3, but the list is growing) sheets. So I would like to have a macro which actually somehow unites the column A of all sheets, and does the search there. I hope you can understand what I mean. [/quote

I was under the impression that you had multiple columns of field/column "Code0" in each worksheet.

In order to resolve your request, please attach another workbook with data in multiple columns, in sheets "Sheet1", "Sheet2", and "Sheet3". Sheet3 in your lastest post is empty


Have a great day,
Stan

[hunsnowboarder]

Hi Leith!
Your macro works great, but just for one worksheet! I need to search all the worksheets in the workbook (the number of worksheets is increasing) in column A. So that is the basic problem. To search for a codenumber and if it did not find it than go to the next sheet and so on till it finds it, or if not found on the last sheet then delete it! Thank you! (You can also check the attached file, the macro should look in every sheet in column A. You can see it in the attached file)

Hi Stan! Please see attached. The file is there, zipped! It is called: Stan-sample.zip. Thank you for your help!

[Leith Ross]

Hello Hun,

I'll change that for you to search all sheets. No problem.

[Leith Ross]

Hello Hun,

The macro will now search all sheets in the workbook, except for the worksheet named "List".

Please Login or Register  to view this content.

[hunsnowboarder]

Hi Leith! I tried the macro, but each time I run it I get run-time error '424': Object required.
The problem seem to be in row: For R = ListRng.Rows.Count To 1 Step -1

Please, could you try to help in this problem?

Thank you!

[stanleydgromjr]

hunsnowboarder,

See the attached workbook "Delete item in a list if not found on multiple sheets - hunsnowboarder - SDG.xls", in "hunsnowboarder.zip".

From the "List" sheet, to run the "DeleteItem" macro: CTRL + SHIFT + D


If I understand you correctly: The macro will search each sheet for the "Code_01" number. If the number is "NOT FOUND" on any of the sheets, then delete the number.


Have a great day,
Stan

[Leith Ross]

Hello Hun,

Third time is the charm. I revised the macro to skip the worksheet if it has no data and it won't delete the entry unless it is missing on all worksheets, "List" excluded.

Please Login or Register  to view this content.

[hunsnowboarder]

Hi Leith! Thank you very much for your help! The macro it is working GREAT! I did not need to delete the whole row so I altered the delet entire row to delete Shift:=xlUp. But it is great, working marveoulusly! Thank you a lot!

Hi Stan! Your code is also good. I will put it in similar file and test both codes! Thank you a lot for your efforts! I relly appreciate it! Thank you a lot!

Thank you all for your help!