DuplicateImageReview.bas

Attribute VB_Name = "DuplicateImageReview"
Option Explicit

' Worksheet layout
Private Const FIRST_DATA_ROW As Long = 2
Private Const FIRST_IMAGE_COL As Long = 2
Private Const LAST_IMAGE_COL As Long = 10

Private Const MANUAL_SHEET As String = "Manual"
Private Const WRITEBUFFER_SHEET As String = "WriteBuffer"
Private Const HIGHLIGHT_MARKER As String = "DUPLICATE_IMAGE_LINK_HIGHLIGHT"
Private Const HIGHLIGHT_BATCH_SIZE As Long = 100

' Button 1: detect column + row duplicate URLs in B:J.
Public Sub DetectDuplicateImageLinks()
    Dim targetSheet As Worksheet
    Dim columnGroupCount As Long
    Dim rowGroupCount As Long
    Dim previousCalculation As XlCalculation
    Dim previousScreenUpdating As Boolean
    Dim previousEnableEvents As Boolean
    Dim settingsSaved As Boolean
    Dim summary As String

    On Error GoTo ErrorHandler

    Set targetSheet = GetTargetWorksheet()
    If targetSheet Is Nothing Then
        MsgBox "This macro can only run on the Manual or WriteBuffer worksheet.", _
               vbExclamation, "Duplicate Image Links"
        Exit Sub
    End If

    SaveExcelSettings previousCalculation, previousScreenUpdating, previousEnableEvents
    settingsSaved = True
    EnablePerformanceMode

    RunDuplicateDetection targetSheet, columnGroupCount, rowGroupCount

    RestoreExcelSettings previousCalculation, previousScreenUpdating, previousEnableEvents
    settingsSaved = False

    If columnGroupCount + rowGroupCount = 0 Then
        summary = "Duplicate scan completed." & vbCrLf & vbCrLf & _
                  "Column duplicate groups: 0" & vbCrLf & _
                  "Row duplicate groups: 0" & vbCrLf & vbCrLf & _
                  "No duplicates detected."
    Else
        summary = "Duplicate scan completed." & vbCrLf & vbCrLf & _
                  "Column duplicate groups: " & Format$(columnGroupCount, "#,##0") & vbCrLf & _
                  "Row duplicate groups: " & Format$(rowGroupCount, "#,##0") & vbCrLf & vbCrLf & _
                  "Total duplicate groups: " & _
                  Format$(columnGroupCount + rowGroupCount, "#,##0")
    End If

    MsgBox summary, vbInformation, "Duplicate Image Links"
    Exit Sub

ErrorHandler:
    If settingsSaved Then
        RestoreExcelSettings previousCalculation, previousScreenUpdating, previousEnableEvents
    End If
    MsgBox "Duplicate detection could not be completed." & vbCrLf & _
           Err.Description, vbCritical, "Duplicate Image Links"
End Sub

' Button 2: remove duplicates only within each individual row.
Public Sub RemoveRowDuplicates()
    Dim targetSheet As Worksheet
    Dim imageData As Variant
    Dim lastDataRow As Long
    Dim dataRowIndex As Long
    Dim duplicateRowsRemoved As Long
    Dim rowGroups As Long
    Dim rowLinks As Long
    Dim columnGroupCount As Long
    Dim rowGroupCount As Long
    Dim previousCalculation As XlCalculation
    Dim previousScreenUpdating As Boolean
    Dim previousEnableEvents As Boolean
    Dim settingsSaved As Boolean

    On Error GoTo ErrorHandler

    Set targetSheet = GetTargetWorksheet()
    If targetSheet Is Nothing Then
        MsgBox "This macro can only run on the Manual or WriteBuffer worksheet.", _
               vbExclamation, "Remove Row Duplicates"
        Exit Sub
    End If

    SaveExcelSettings previousCalculation, previousScreenUpdating, previousEnableEvents
    settingsSaved = True
    EnablePerformanceMode

    lastDataRow = GetLastRow(targetSheet)
    If lastDataRow >= FIRST_DATA_ROW Then
        imageData = targetSheet.Range( _
            targetSheet.Cells(FIRST_DATA_ROW, FIRST_IMAGE_COL), _
            targetSheet.Cells(lastDataRow, LAST_IMAGE_COL)).Value2

        For dataRowIndex = 1 To UBound(imageData, 1)
            CompressRow imageData, dataRowIndex, rowGroups, rowLinks
            If rowLinks > 0 Then duplicateRowsRemoved = duplicateRowsRemoved + 1
        Next dataRowIndex

        ' Assigning values preserves all cell formatting.
        targetSheet.Range( _
            targetSheet.Cells(FIRST_DATA_ROW, FIRST_IMAGE_COL), _
            targetSheet.Cells(lastDataRow, LAST_IMAGE_COL)).Value2 = imageData
    End If

    ' Refresh highlights without showing the Detect Duplicates popup.
    RunDuplicateDetection targetSheet, columnGroupCount, rowGroupCount

    RestoreExcelSettings previousCalculation, previousScreenUpdating, previousEnableEvents
    settingsSaved = False

    MsgBox "Duplicate rows removed: " & Format$(duplicateRowsRemoved, "#,##0"), _
           vbInformation, "Remove Row Duplicates"
    Exit Sub

ErrorHandler:
    If settingsSaved Then
        RestoreExcelSettings previousCalculation, previousScreenUpdating, previousEnableEvents
    End If
    MsgBox "Row duplicates could not be removed." & vbCrLf & _
           Err.Description, vbCritical, "Remove Row Duplicates"
End Sub

' Clear previous highlights, then scan and highlight column + row duplicate groups.
Private Sub RunDuplicateDetection( _
    ByVal targetSheet As Worksheet, _
    ByRef columnGroupCount As Long, _
    ByRef rowGroupCount As Long)

    Dim lastDataRow As Long
    Dim imageData As Variant
    Dim highlightGroups As Object
    Dim groupOrder As Collection
    Dim usedColors As Object
    Dim groupKey As Variant
    Dim occurrenceList As Collection
    Dim fillColor As Long
    Dim highlightIndex As Long

    columnGroupCount = 0
    rowGroupCount = 0

    ClearDuplicateHighlights targetSheet

    lastDataRow = GetLastRow(targetSheet)
    If lastDataRow < FIRST_DATA_ROW Then Exit Sub

    imageData = targetSheet.Range( _
        targetSheet.Cells(FIRST_DATA_ROW, FIRST_IMAGE_COL), _
        targetSheet.Cells(lastDataRow, LAST_IMAGE_COL)).Value2

    Set highlightGroups = CreateObject("Scripting.Dictionary")
    highlightGroups.CompareMode = vbTextCompare
    Set groupOrder = New Collection

    CountAndCollectDuplicates imageData, highlightGroups, groupOrder, _
                              columnGroupCount, rowGroupCount

    Set usedColors = CreateObject("Scripting.Dictionary")

    For Each groupKey In groupOrder
        Set occurrenceList = highlightGroups.Item(CStr(groupKey))
        If occurrenceList.Count > 1 Then
            highlightIndex = highlightIndex + 1
            fillColor = GetGroupColor(highlightIndex, usedColors)
            HighlightOccurrences targetSheet, occurrenceList, fillColor, highlightIndex
        End If
    Next groupKey
End Sub

' Column group = same URL in 2+ distinct rows.
' Row group = same URL 2+ times in one row.
' Highlight map is keyed by URL so every occurrence shares one colour.
Private Sub CountAndCollectDuplicates( _
    ByVal imageData As Variant, _
    ByVal highlightGroups As Object, _
    ByVal groupOrder As Collection, _
    ByRef columnGroupCount As Long, _
    ByRef rowGroupCount As Long)

    Dim urlRows As Object
    Dim urlRowSet As Object
    Dim rowUrlCounts As Object
    Dim dataRowIndex As Long
    Dim dataColumnIndex As Long
    Dim worksheetRow As Long
    Dim worksheetColumn As Long
    Dim normalizedUrl As String
    Dim cellValue As Variant
    Dim occurrenceList As Collection
    Dim urlKey As Variant
    Dim countKey As Variant

    Set urlRows = CreateObject("Scripting.Dictionary")
    urlRows.CompareMode = vbTextCompare

    For dataRowIndex = 1 To UBound(imageData, 1)
        worksheetRow = dataRowIndex + FIRST_DATA_ROW - 1
        Set rowUrlCounts = CreateObject("Scripting.Dictionary")
        rowUrlCounts.CompareMode = vbTextCompare

        For dataColumnIndex = 1 To UBound(imageData, 2)
            cellValue = imageData(dataRowIndex, dataColumnIndex)
            If IsError(cellValue) Then GoTo NextCell

            normalizedUrl = NormalizeUrl(cellValue)
            If Len(normalizedUrl) = 0 Then GoTo NextCell

            worksheetColumn = dataColumnIndex + FIRST_IMAGE_COL - 1

            If Not highlightGroups.Exists(normalizedUrl) Then
                Set occurrenceList = New Collection
                highlightGroups.Add normalizedUrl, occurrenceList
                groupOrder.Add normalizedUrl
            Else
                Set occurrenceList = highlightGroups.Item(normalizedUrl)
            End If
            occurrenceList.Add Array(worksheetRow, worksheetColumn)

            If Not urlRows.Exists(normalizedUrl) Then
                Set urlRowSet = CreateObject("Scripting.Dictionary")
                urlRows.Add normalizedUrl, urlRowSet
            Else
                Set urlRowSet = urlRows.Item(normalizedUrl)
            End If
            urlRowSet(CStr(worksheetRow)) = True

            If rowUrlCounts.Exists(normalizedUrl) Then
                rowUrlCounts(normalizedUrl) = CLng(rowUrlCounts(normalizedUrl)) + 1
            Else
                rowUrlCounts.Add normalizedUrl, 1
            End If
NextCell:
        Next dataColumnIndex

        For Each countKey In rowUrlCounts.Keys
            If CLng(rowUrlCounts(countKey)) > 1 Then
                rowGroupCount = rowGroupCount + 1
            End If
        Next countKey
    Next dataRowIndex

    For Each urlKey In urlRows.Keys
        Set urlRowSet = urlRows.Item(urlKey)
        If urlRowSet.Count > 1 Then
            columnGroupCount = columnGroupCount + 1
        End If
    Next urlKey
End Sub

' Batch noncontiguous cells to avoid one rule per duplicate cell.
Private Sub HighlightOccurrences( _
    ByVal targetSheet As Worksheet, _
    ByVal occurrenceList As Collection, _
    ByVal fillColor As Long, _
    ByVal groupNumber As Long)

    Dim occurrence As Variant
    Dim batchRange As Range
    Dim occurrenceCell As Range
    Dim batchCount As Long

    For Each occurrence In occurrenceList
        Set occurrenceCell = targetSheet.Cells( _
            CLng(occurrence(LBound(occurrence))), _
            CLng(occurrence(UBound(occurrence))))

        If batchRange Is Nothing Then
            Set batchRange = occurrenceCell
        Else
            Set batchRange = Application.Union(batchRange, occurrenceCell)
        End If

        batchCount = batchCount + 1
        If batchCount >= HIGHLIGHT_BATCH_SIZE Then
            AddHighlightRule batchRange, fillColor, groupNumber
            Set batchRange = Nothing
            batchCount = 0
        End If
    Next occurrence

    If Not batchRange Is Nothing Then
        AddHighlightRule batchRange, fillColor, groupNumber
    End If
End Sub

' Conditional formatting preserves the cells' permanent formatting.
Private Sub AddHighlightRule( _
    ByVal targetRange As Range, _
    ByVal fillColor As Long, _
    ByVal groupNumber As Long)

    Dim highlightRule As Object
    Dim markerFormula As String

    markerFormula = "=N(""" & HIGHLIGHT_MARKER & _
                    "_" & CStr(groupNumber) & """)=0"

    Set highlightRule = targetRange.FormatConditions.Add( _
        Type:=xlExpression, Formula1:=markerFormula)

    highlightRule.Interior.Color = fillColor
    highlightRule.StopIfTrue = False
End Sub

' Remove only conditional-format rules created by this module.
Public Sub ClearDuplicateHighlights(ByVal targetSheet As Worksheet)
    Dim formatIndex As Long
    Dim formatRule As Object

    If targetSheet Is Nothing Then Exit Sub

    For formatIndex = targetSheet.Cells.FormatConditions.Count To 1 Step -1
        Set formatRule = targetSheet.Cells.FormatConditions(formatIndex)
        If IsDuplicateHighlightRule(formatRule) Then formatRule.Delete
    Next formatIndex
End Sub

' Drop duplicate highlight rules when product data is edited or cleared.
Public Sub OnDataAreaChanged( _
    ByVal targetSheet As Worksheet, _
    ByVal changedRange As Range)

    Dim dataRange As Range

    If targetSheet Is Nothing Or changedRange Is Nothing Then Exit Sub
    If Not IsTargetWorksheet(targetSheet) Then Exit Sub

    Set dataRange = Intersect( _
        changedRange, _
        targetSheet.Range( _
            targetSheet.Cells(FIRST_DATA_ROW, 1), _
            targetSheet.Cells(targetSheet.Rows.Count, LAST_IMAGE_COL)))

    If Not dataRange Is Nothing Then ClearDuplicateHighlights targetSheet
End Sub

Private Function IsDuplicateHighlightRule( _
    ByVal formatRule As Object) As Boolean

    Dim formulaText As String
    On Error GoTo NotDuplicateRule

    If TypeName(formatRule) <> "FormatCondition" Then Exit Function
    If formatRule.Type <> xlExpression Then Exit Function

    formulaText = CStr(formatRule.Formula1)
    IsDuplicateHighlightRule = _
        (InStr(1, formulaText, HIGHLIGHT_MARKER, vbTextCompare) > 0)
    Exit Function

NotDuplicateRule:
    IsDuplicateHighlightRule = False
End Function

' Deduplicate and compact one row. Returns row-group and link-removal counts.
Private Sub CompressRow( _
    ByRef imageData As Variant, _
    ByVal dataRowIndex As Long, _
    ByRef rowGroupsRemoved As Long, _
    ByRef linksRemoved As Long)

    Dim seenUrls As Object
    Dim urlCounts As Object
    Dim retainedValues() As Variant
    Dim imageColumnCount As Long
    Dim inputColumn As Long
    Dim outputColumn As Long
    Dim cellValue As Variant
    Dim normalizedUrl As String
    Dim countKey As Variant

    rowGroupsRemoved = 0
    linksRemoved = 0
    imageColumnCount = LAST_IMAGE_COL - FIRST_IMAGE_COL + 1
    ReDim retainedValues(1 To imageColumnCount)

    Set seenUrls = CreateObject("Scripting.Dictionary")
    seenUrls.CompareMode = vbTextCompare
    Set urlCounts = CreateObject("Scripting.Dictionary")
    urlCounts.CompareMode = vbTextCompare

    For inputColumn = 1 To imageColumnCount
        cellValue = imageData(dataRowIndex, inputColumn)

        If IsError(cellValue) Then
            outputColumn = outputColumn + 1
            retainedValues(outputColumn) = cellValue
        Else
            normalizedUrl = NormalizeUrl(cellValue)

            If Len(normalizedUrl) > 0 Then
                If urlCounts.Exists(normalizedUrl) Then
                    urlCounts(normalizedUrl) = CLng(urlCounts(normalizedUrl)) + 1
                Else
                    urlCounts.Add normalizedUrl, 1
                End If

                If Not seenUrls.Exists(normalizedUrl) Then
                    seenUrls.Add normalizedUrl, True
                    outputColumn = outputColumn + 1
                    retainedValues(outputColumn) = cellValue
                Else
                    linksRemoved = linksRemoved + 1
                End If
            End If
        End If
    Next inputColumn

    For Each countKey In urlCounts.Keys
        If CLng(urlCounts(countKey)) > 1 Then
            rowGroupsRemoved = rowGroupsRemoved + 1
        End If
    Next countKey

    For inputColumn = 1 To imageColumnCount
        If inputColumn <= outputColumn Then
            imageData(dataRowIndex, inputColumn) = retainedValues(inputColumn)
        Else
            imageData(dataRowIndex, inputColumn) = Empty
        End If
    Next inputColumn
End Sub

Private Function GetGroupColor( _
    ByVal groupNumber As Long, _
    ByVal usedColors As Object) As Long

    Dim colorPalette As Variant
    Dim candidateColor As Long
    Dim calculatedColor As Double
    Dim colorKey As String

    colorPalette = Array( _
        RGB(255, 235, 59), RGB(129, 199, 132), _
        RGB(100, 181, 246), RGB(255, 183, 77), _
        RGB(244, 143, 177), RGB(171, 71, 188), _
        RGB(77, 208, 225), RGB(174, 213, 129), _
        RGB(255, 138, 101), RGB(159, 168, 218), _
        RGB(255, 202, 40), RGB(38, 166, 154))

    If groupNumber <= UBound(colorPalette) + 1 Then
        candidateColor = colorPalette(groupNumber - 1)
    Else
        calculatedColor = groupNumber * 2654435761#
        calculatedColor = calculatedColor - _
            Int(calculatedColor / 16777216#) * 16777216#
        candidateColor = CLng(calculatedColor)
    End If

    colorKey = CStr(candidateColor)
    Do While usedColors.Exists(colorKey)
        candidateColor = candidateColor + 1
        If candidateColor > 16777215 Then candidateColor = 0
        colorKey = CStr(candidateColor)
    Loop

    usedColors.Add colorKey, True
    GetGroupColor = candidateColor
End Function

Private Function GetLastRow(ByVal targetSheet As Worksheet) As Long
    GetLastRow = targetSheet.Cells( _
        targetSheet.Rows.Count, 1).End(xlUp).Row
End Function

Private Function GetTargetWorksheet() As Worksheet
    If TypeName(ActiveSheet) <> "Worksheet" Then Exit Function
    If IsTargetWorksheet(ActiveSheet) Then Set GetTargetWorksheet = ActiveSheet
End Function

Private Function IsTargetWorksheet(ByVal targetSheet As Worksheet) As Boolean
    IsTargetWorksheet = _
        (StrComp(targetSheet.Name, MANUAL_SHEET, vbTextCompare) = 0) Or _
        (StrComp(targetSheet.Name, WRITEBUFFER_SHEET, vbTextCompare) = 0)
End Function

Private Function NormalizeUrl(ByVal cellValue As Variant) As String
    On Error GoTo NormalizeFailed

    If IsError(cellValue) Or IsEmpty(cellValue) Or IsNull(cellValue) Then
        Exit Function
    End If

    If VarType(cellValue) = vbObject Or VarType(cellValue) = vbDataObject Then
        Exit Function
    End If

    NormalizeUrl = LCase$(Trim$(CStr(cellValue)))
    Exit Function

NormalizeFailed:
    NormalizeUrl = vbNullString
End Function

Private Sub SaveExcelSettings( _
    ByRef calculationMode As XlCalculation, _
    ByRef screenUpdating As Boolean, _
    ByRef enableEvents As Boolean)

    calculationMode = Application.Calculation
    screenUpdating = Application.ScreenUpdating
    enableEvents = Application.EnableEvents
End Sub

Private Sub EnablePerformanceMode()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
End Sub

Private Sub RestoreExcelSettings( _
    ByVal calculationMode As XlCalculation, _
    ByVal screenUpdating As Boolean, _
    ByVal enableEvents As Boolean)

    Application.Calculation = calculationMode
    Application.ScreenUpdating = screenUpdating
    Application.EnableEvents = enableEvents
End Sub