+ Reply to Thread
Results 1 to 18 of 18

Macro to delete item if not found on a list

  1. #1
    Forum Contributor
    Join Date
    08-01-2007
    Posts
    110

    Macro to delete item if not found on a list

    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!
    Attached Files Attached Files
    Last edited by hunsnowboarder; 01-28-2009 at 06:11 PM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Macro to delete item if not found on a list

    X-Post

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

  3. #3
    Forum Contributor
    Join Date
    08-01-2007
    Posts
    110

    Re: Macro to delete item if not found on a list

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

  4. #4
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Macro to delete item if not found on a list

    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
    Attached Files Attached Files
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  5. #5
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Macro to delete item if not found on a list

    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

  6. #6
    Forum Contributor
    Join Date
    08-01-2007
    Posts
    110

    Re: Macro to delete item if not found on a list

    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!
    Attached Files Attached Files
    Last edited by hunsnowboarder; 01-26-2009 at 07:27 PM. Reason: added attachment

  7. #7
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Macro to delete item if not found on a list

    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

  8. #8
    Forum Contributor
    Join Date
    08-01-2007
    Posts
    110

    Re: Macro to delete item if not found on a list

    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!

  9. #9
    Forum Contributor
    Join Date
    08-01-2007
    Posts
    110

    Re: Macro to delete item if not found on a list

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

  10. #10
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to delete item if not found on a list

    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.
    Attached Files Attached Files
    Last edited by Leith Ross; 01-27-2009 at 05:11 PM.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  11. #11
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Macro to delete item if not found on a list

    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
    Last edited by Leith Ross; 01-27-2009 at 05:54 PM.

  12. #12
    Forum Contributor
    Join Date
    08-01-2007
    Posts
    110

    Re: Macro to delete item if not found on a list

    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!
    Attached Files Attached Files
    Last edited by hunsnowboarder; 01-27-2009 at 06:08 PM. Reason: Explanation added

  13. #13
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to delete item if not found on a list

    Hello Hun,

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

  14. #14
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to delete item if not found on a list

    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.
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    08-01-2007
    Posts
    110

    Re: Macro to delete item if not found on a list

    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!

  16. #16
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Macro to delete item if not found on a list

    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
    Attached Files Attached Files

  17. #17
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to delete item if not found on a list

    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.
    Attached Files Attached Files
    Last edited by Leith Ross; 01-28-2009 at 02:35 PM. Reason: Uploaded wrong file

  18. #18
    Forum Contributor
    Join Date
    08-01-2007
    Posts
    110

    Re: Macro to delete item if not found on a list

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1