VBA Msgbox if duplicate exists

[snjpverma]

In the below data, only the ones highlighted in Orange are duplicates.

I want a code to check if there are any duplicates in the sheet. If duplicates exist then it should give a message box stating "please remove the duplicates".

the columns to be checked for duplicates are Column C & column F in combination (not individually).
The below data is just a sample.

Names	Product Status
Alan	Returned
Barbara	Returned
Charlie	Sold
Dave	Sold
Alan	Sold
Huzbert	Sold
Barbara	Sold
Inez	Sold
Mark	Sold
Barbara	Sold
Norah	Sold

 

[snjpverma]

Cross posting link: Cross-posted here

 

[Fluff]

Why not just use conditional formatting & use this formula
=COUNTIFS($C$2:$C$100,$C2,$F$2:$F$100,$F2)>1

 

[snjpverma]

Why not just use conditional formatting & use this formula
=COUNTIFS($C$2:$C$100,$C2,$F$2:$F$100,$F2)>1

Thanks for your reply Fluff.
However, I was planning to add this in my existing code which does some other formatting within the sheets.
However, before i run the macro, I want to ensure that there are no duplicates in there.

Putting a conditional formatting will not justify my purpose of time saving and highlighting the column isn't of any use to me.
I will have to remove the formatting manually after checking and deleting the duplicates.

 

[Fluff]

You said

If duplicates exist then it should give a message box stating "please remove the duplicates".

All that does is tell you there are duplicates, but where. So you then have to hunt round & find them, how is that quicker than using CF to highlight the dupes? That at least shows you what is duplicated.

 

[snjpverma]

I vouching for macro because the chance of duplication is less than 1%.
But you have a good point there. Just knowing if the duplicates exist won't be of any help.

I appreciate your suggestion and your solution in Post # 3 can be created into a macro.

I tried it myself but it's giving an error. Please advise why the autofilter line gives an error "application defined error 1004"

Sub checkduplicates()
Dim x As Boolean
    With Range("G1")
        .Select
        .EntireColumn.Insert xlToRight
        .Value = "Duplicates"
    End With
    
    With Range("G2:G300")
        .Select
        .Formula = "=CountIfs($C$2:$C$100,$C2,$F$2:$F$100,$F2)>1"
        .AutoFilter Field:=6, Criteria1:="TRUE"
     End With

x = MsgBox("Duplicates found?", vbYesNo)
    If x = True Then Exit Sub
    Else

'my rest of the code.

End Sub

 

[Fluff]

How about

Sub checkduplicates()
   
   Range("G1").EntireColumn.Insert xlToRight
   Range("G1").Value = "Duplicates"
    
   With Range("G1:G" & Range("F" & Rows.Count).End(xlUp).Row)
      .Offset(1).FormulaR1C1 = "=CountIfs(C3,rC3,c6,rc6)>1"
      .AutoFilter Field:=1, Criteria1:=True
      If .SpecialCells(xlVisible).Count = 1 Then
         .AutoFilter
         MsgBox "No dupes"
      Else
         
         MsgBox "Duplicates"
         Exit Sub
      End If
   End With
'my rest of the code.

End Sub

 

[snjpverma]

Thanks Fluff,
that works exactly as intended.

Request you to answer the below two question for my knowledge though.

why does the below works if Field is "1" instead of 6? When I had used a macro recorder, it gave me as field:=6

 .AutoFilter Field:=1, Criteria1:=True

does this count how many rows are available after the filtering?

      If .SpecialCells(xlVisible).Count = 1 Then

[/CODE]

 

[Fluff]

If you filtered A1:G1 then field would need to be 6 as it's the 6th column, but as the code is only filtering col G it needs to be 1 as it's the 1st column being filtered.
The 2nd part counts the number of visible cells in the range. If there are no dupes, then G1 will be the only visible cell & that code will return 1

 

[snjpverma]

If you filtered A1:G1 then field would need to be 6 as it's the 6th column, but as the code is only filtering col G it needs to be 1 as it's the 1st column being filtered.
The 2nd part counts the number of visible cells in the range. If there are no dupes, then G1 will be the only visible cell & that code will return 1

Wow, thanks.
Now I see why recording chose "6" in the field. It's because the filter through Ctrl + Shift +L keyword shortcut works on the current region.