expand duplicate check to multiple columns

[Horatio J. Bilge, Jr.]

I wrote some code to check a column for duplicates. If duplicates are found,
a message box shows that the column has duplicate values. I want to expand
the code to include multiple columns, so the message box will then list all
of the columns that have duplicates. My code checks column B - I want to also
check C through M. The data is in rows 3 - 42, and column headers are in row
2.

Here is my code:
Sub DupeCheck()
Dim r As Integer
Dim m As String

For r = 42 To 3 Step -1
If Cells(r, "B").Value = "" Then
GoTo EmptyCell
End If
If Application.WorksheetFunction.CountIf(Range("B1:B" & r), Range("B" &
r).Text) > 1 Then
m = m & vbLf & Cells(2, "B").Value
Exit For
End If
EmptyCell:
Next r

MsgBox "These columns have duplicates: " & vbLf & m

End Sub

 

[Dave Peterson]

I checked .value to .value in the =countif() expression. I'm not sure why you
used .text. But if you needed that, you'll have to change it back:

Option Explicit
Sub DupeCheck()
Dim iRow As Long
Dim iCol As Long
Dim m As String

m = ""
With ActiveSheet
For iCol = .Range("B1").Column To .Range("M1").Column
For iRow = 42 To 3 Step -1
If .Cells(iRow, iCol).Value = "" Then
'skip it
Else
If Application.CountIf(.Cells(1, iCol).Resize(iRow, 1), _
.Cells(iRow, iCol).Value) > 1 Then
m = m & vbLf & .Cells(2, iCol).Value
Exit For
End If
End If
Next iRow
Next iCol
End With

If m = "" Then
MsgBox "no duplicates"
Else
MsgBox "These columns have duplicates: " & vbLf & m
End If

End Sub

You may want to look at some of the techniques Chip Pearson uses to highlight
duplicates.
http://www.cpearson.com/excel/Duplicates.aspx

 

[Horatio J. Bilge, Jr.]

That works great. Why I used .text -- I found a Delete_Dupes macro, and
tweaked it for my needs. That macro used the .text, so I left it that way.
Using .value makes more sense, though.

Thanks for the help!
~ Horatio

 

[Horatio J. Bilge, Jr.]

I used one of Chip Pearson's highlighting techniques in a different project,
but I wanted to go a different route for this one.