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


* 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

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

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

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext