Databases
StackOverflow2013
Postsdbo
POHow do I delete duplicates between two excel sheets quickly vba
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I delete duplicates between two excel sheets quickly vba
    primarykey
    data
    text
    <p>I am using vba and I have two sheets one is named "Do Not Call" and has about 800,000 rows of data in column A. I want to use this data to check column I in the second sheet, named "Sheet1". If it finds a match I want it to delete the whole row in "Sheet1". I have tailored the code I have found from a similar question here: <a href="https://stackoverflow.com/questions/3426205/excel-formula-to-cross-reference-2-sheets-remove-duplicates-from-one-sheet">Excel formula to Cross reference 2 sheets, remove duplicates from one sheet</a> and ran it but nothing happens. I am not getting any errors but it is not functioning. </p> <p>Here is the code I am currently trying and have no idea why it is not working</p> <pre><code>Option Explicit Sub CleanDupes() Dim wsA As Worksheet Dim wsB As Worksheet Dim keyColA As String Dim keyColB As String Dim rngA As Range Dim rngB As Range Dim intRowCounterA As Integer Dim intRowCounterB As Integer Dim strValueA As String keyColA = "A" keyColB = "I" intRowCounterA = 1 intRowCounterB = 1 Set wsA = Worksheets("Do Not Call") Set wsB = Worksheets("Sheet1") Dim dict As Object Set dict = CreateObject("Scripting.Dictionary") Do While Not IsEmpty(wsA.Range(keyColA &amp; intRowCounterA).Value) Set rngA = wsA.Range(keyColA &amp; intRowCounterA) strValueA = rngA.Value If Not dict.Exists(strValueA) Then dict.Add strValueA, 1 End If intRowCounterA = intRowCounterA + 1 Loop intRowCounterB = 1 Do While Not IsEmpty(wsB.Range(keyColB &amp; intRowCounterB).Value) Set rngB = wsB.Range(keyColB &amp; intRowCounterB) If dict.Exists(rngB.Value) Then wsB.Rows(intRowCounterB).delete intRowCounterB = intRowCounterB - 1 End If intRowCounterB = intRowCounterB + 1 Loop End Sub </code></pre> <p>I apologize if the above code is not in a code tag. This is my first time posting code online and I have no idea if I did it correctly.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USMainTank
    plurals
    1. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow do I delete duplicates between two excel sheets quickly vba
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow do I delete duplicates between two excel sheets quickly vba
      1. This table or related slice is empty.
      1. VTUpMod
    1. COdoes it need to be VBA. I think a vlookup could make this pretty easy, unless of course you need to do this over and over? Happy to help with the VBA, just might be overkill?
      singulars
      1. POHow do I delete duplicates between two excel sheets quickly vba
      1. USRoss
    2. COThanks for the fast response! I considered vlookup but honestly I wasn't sure if it could do what I wanted. Sadly I am not to familiar with that function. I would need to use this for other sheets (replacing only Sheet1 not the Do Not Call Sheet). Is vlookup something I should look into if I am planning on doing this with several other sheets?
      singulars
      1. POHow do I delete duplicates between two excel sheets quickly vba
      1. USMainTank
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload