DUPLICATECELLS

Returns the cell addresses of all the items that appear more than once.

Remarks

* For instructions on how to add a function to a workbook refer to the page under Inserting Functions
* The equivalent JavaScript function is DUPLICATECELLS

Public Function DUPLICATECELLS(ByVal rgeValues As Range, _ 
                      Optional ByVal bAsArray As Boolean = False) As Variant 

Dim lcellcount As Long 
Dim rgeCell As Range 
Dim lmatchindex As Long 
Dim sduplicateaddresses As String 
Dim aTheDuplicates As Variant 

    For lcellcount = 1 To rgeValues.Cells.Count 
       Set rgeCell = rgeValues.Cells(lcellcount) 
    
       If (rgeCell.Value <> "") Then 
      
          lmatchindex = Application.WorksheetFunction.Match(rgeCell, rgeValues, 0) 
          
          If (lmatchindex <> lcellcount) Then 
             sduplicateaddresses = sduplicateaddresses & "," & rgeCell.Address(False, False) 
          End If 
       End If 
    Next 

   If (Len(sduplicateaddresses) = 0) Then 
      DUPLICATECELLS = "no duplicates" 
   Else 
      sduplicateaddresses = Right(sduplicateaddresses, Len(sduplicateaddresses) - 1) 
   End If 

   If (bAsArray = False) Then 
      DUPLICATECELLS = sduplicateaddresses 
   Else 
      aTheDuplicates = VBA.Split(sduplicateaddresses, ",") 
      aTheDuplicates = Application.WorksheetFunction.Transpose(aTheDuplicates) 
      DUPLICATECELLS = aTheDuplicates 
   End If 
       
End Function