Using VBA to check duplicate entries and create MsgBox

Joey_T92

New Member
Joined
Mar 1, 2021
Messages
1
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hello,

I'm having a bit of trouble if someone would be able to help. I've had a look and can't seem to find anywhere this has been addressed previously.

I have CSV files containing customer data where each row is a new customer and the columns take the form: first name, last name, address 1, address 2, address 3, city, postcode, etc... I have a series of macros which manipulate the data in these files and then export it in multiple formats as required by various other software packages.

For reasons mostly outside of my control, some customers details are duplicated during the process which gathers this information, but I have a macro to identify and remove these duplicate rows. This macro essentially looks at the first name, last name, and address columns and removes one of the duplicates if all these fields match:

VBA Code:
Sub DelDups()
    
    With Sheets("Master")
        .Cells.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8)
    End With

End Sub

Unfortunately it has now become apparent that some customers are being entered in to the front end system twice but with two different addresses. This results in two orders being generated and dispatched as these duplicates are not detected by the current macro. What I want to do is have a macro I can run after the above has finished and which will check for duplicates in the first and last name fields (columns A and B), and if any are found it will trigger a MsgBox asking the user to manually check the raw CSV to identify the potential duplication and delete the entry with the incorrect address (if required).

Essentially:
If there are rows with duplicate values in columns A and B then pop up a message to signal this, if the yes button is clicked then continue processing, if the cancel/no button is clicked then abort any further processing.

Hopefully this makes sense. I'm new to VBA so I'm trying to learn, and what I've been able to do so far is great, but still find some of it very confusing!

Thanks very much for your help!
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
.
The following will check both columns (A&B) for duplicates. If located, it will highlight in yellow the second entry of the duplicate, then
popup a MsgBox advising "Duplicates".

VBA Code:
Option Explicit

Sub CheckDupes()

   Dim Cl As Range
   Dim ValU As String
   Dim Rng As Range
  
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
         ValU = Cl.Value & Cl.Offset(, 1).Value
         If Not .exists(ValU) Then
            .Add ValU, Nothing
         Else
            If Rng Is Nothing Then
               Set Rng = Cl
            Else
               Set Rng = Union(Rng, Cl)
               Rng.Interior.Color = vbYellow
            End If
         End If
      Next Cl
   End With
   If Not Rng Is Nothing Then
    MsgBox "Duplicates"
   End If

End Sub
 
Upvote 0
Hi, looking for something similar.
How to adapt this to work with this:

-search for duplicates under column A,
- some can have spaces before and/or after the inserted data
-highlight the duplicated cells in yellow (all of them);
-message box to appear when duplicate is inserted or when trying to close the file via Save/save as
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,180
Members
452,615
Latest member
bogeys2birdies

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