Macro to delete item if not found on a list

H

hunsnowboarder

New Member
Joined
Dec 3, 2008
Messages
23
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"><meta name="ProgId" content="Word.Document"><meta name="Generator" content="Microsoft Word 11"><meta name="Originator" content="Microsoft Word 11"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5COsika%5CLOCALS%7E1%5CTemp%5Cmsohtml1%5C01%5Cclip_filelist.xml"><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:HyphenationZone>21</w:HyphenationZone> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><style> <!-- /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman";} @page Section1 {size:595.3pt 841.9pt; margin:70.85pt 70.85pt 70.85pt 70.85pt; mso-header-margin:35.4pt; mso-footer-margin:35.4pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Normál táblázat"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Hi there Everyone! <o:p></o:p>
Please try to help me if you can with my problem. <o:p></o:p>
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 . <o:p></o:p>
On the "List" worksheet I have a smaller list <o:p></o:p>
I have a code also in module1 but the code is not working.<o:p></o:p>
<o:p> </o:p>
I would like my macro to do the following. Check the codenumber from the "List" worksheet and search for it in "Sheet1" column A. If this codenumber can be found in „Sheet1”column A then leave the number on the list. If it cannot be found in column A then, delete it from the list. I hope you could understand my problem!<o:p></o:p>
Thank you in advance for your help!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Try

Code: 
Sub Try()
Dim LR As Long, i As Long
With Sheets("List")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If IsError(Application.Match(.Range("A" & i).Value, Sheets("Sheet1").Columns("A"), 0)) Then .Range("A" & i).ClearContents
    Next i
End With
End Sub
 
Upvote 0
Hi VoG! Thank you for your quick reply! The code works great! The only problem is that if I use it for multiple sheets, it deletes all the items from the list. Here is my code:

Code: 
Sub Try()
Dim i As Long
Dim ToFind As Range
Dim ws As Worksheet

i = 7
Do While Worksheets(4).Cells(i, "d").Value <> ""
Set ToFind = Worksheets(4).Cells(i, "d")
For Each ws In ActiveWorkbook.Worksheets
    If IsError(Application.Match(ToFind.Value, ws.Columns("A"), 0)) Then ToFind.ClearContents
    Next ws
    i = i + 1
    Loop
 End Sub

As I put the code for each worksheet, this code is not a solution. This code would work only for one worksheet, but in case of many worksheets the code deletes all the items from the list! Maybe you can help me! Thank you!
 
Upvote 0
You seem to have changed the problem.

Maybe

Code: 
Sub Try()
Dim LR As Long, i As Long, ws As Worksheet
With Sheets("List")
    LR = .Range("D" & Rows.Count).End(xlUp).Row
    For i = 7 To LR
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "List" Then
                If IsError(Application.Match(.Range("D" & i).Value, ws.Columns("A"), 0)) Then .Range("D" & i).ClearContents
            End If
        Next ws
    Next i
End With
End Sub
 
Upvote 0
hunsnowboarder, re x-post, your other post was created at 5:02pm UK Time with no link to your thread here, should you x-post all we ask is that you provide links - these are the rules of both forums... on some forums x-posting is not permitted at all (ie you would be banned for doing so).

Thank you for your co-operation.
 
Upvote 0
Yes, DonkeyOte, as I said, I wanted to post here that I have posted on the excelforum site as well, but when I tried to edit my post here, it said that for 10 minutes I cannot edit. Anyway, I apologies. Thank you for remindig me!
 
Upvote 0
Hello VoG! You missunderstood my problem. Probably because of my bad english. :( The code will look for the items in the list on every workbook. Now let's say that there is a code number: 3. The macro will check if in sheet1 is there a 3 in the first column. If there is, it won't delete it. Then it will check the next sheet, column A. If there is no 3 then it will delete it! But this is WRONG! If it found already once (on any sheet) than it should leave there and not delete it! This is my problem...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
M
  • Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
D
  • Question
UserForm within Macro
Replies
1
Views
1K
DanteAmor
DanteAmor
C
  • Question
3 Vlookups from 2 Values
Replies
1
Views
587
AlanY
A
Gerald Higgins
  • Question
VBA, make code move across from main file to extract file
Replies
9
Views
1K
Gerald Higgins
Gerald Higgins

Forum statistics

Threads
1,214,631
Messages
6,120,645
Members
448,974
Latest member
DumbFinanceBro

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top