Hide the visible duplicate cells by using a helper column

Question

The below code is used to hide the visible duplicate cells (rows).
It works , But If I later used autofiletr on any column then all the hidden rows are shown again.
My aim is to use a helper column and set filter on value e,g (False).
In advance, thanks for any help.

Function Hide_visible_duplicate_cells(procRng As Range)
 
    Dim arng As Range, rngU As Range, C As Range, dict As New Scripting.Dictionary
 
    Set arng = procRng.SpecialCells(xlCellTypeVisible)
 
    If arng Is Nothing Then MsgBox "Not a valid Range": Exit Function
 
    For Each C In arng.Cells
        If Not dict.Exists(C.value) Then
            dict.Add C.value, vbNullString       'Keep the first occurrence
        Else
          If rngU Is Nothing Then                'Create a Union range for the next occurrences:
             Set rngU = C
          Else
             Set rngU = Union(rngU, C)
          End If
        End If
    Next C
 
   If Not rngU Is Nothing Then rngU.EntireRow.Hidden = True    'Hide the rows at once
 
End Function

Answer 1

Please, try the next way. But you need to use the column range which you need to be processed, taking care that its first row to be the column headers one:

Sub Hide_visible_duplicate_cells_(procRng As Range)
    Dim arng As Range, C As Range, dict As New Scripting.Dictionary
    Dim arrMark, colMark As Range, lastC As Long, sh As Worksheet, lastR As Long, i As Long
    
    Const markName As String = "Marker_column"
    
    Set arng = procRng.SpecialCells(xlCellTypeVisible)
    
    If arng Is Nothing Then MsgBox "Not a valid Range": Exit Sub
    
    Set sh = procRng.Parent 'the sheet where the range belongs to

    lastR = sh.UsedRange.rows(sh.UsedRange.rows.count).row  'last row OF THE SHEET
    ReDim arrMark(1 To lastR, 1 To 1) 'redim the markers array
    
    'determinte the column where the marker to be placed (or it already exists):
    Set colMark = sh.rows(procRng.cells(1).row).Find(What:=markName, LookIn:=xlValues, LookAt:=xlWhole)
    If Not colMark Is Nothing Then
        lastC = colMark.column  'for the case when the marker column exists
    Else
        lastC = sh.cells(procRng.cells(1).row, sh.Columns.count).End(xlToLeft).column + 1 'next empty column if marker column does not exist
        'to correct the last column number, IF LAST COLUMN IS HIDDEN (it MUST HAVE A HEADER):
        If sh.cells(procRng.cells(1).row, lastC).Value <> "" Then lastC = lastC + 1
    End If
    
    For Each C In arng.cells
        If Not dict.Exists(C.Value) Then
            If i > 0 Then                                            'to skip the first cell, which should be on the headers row
                dict.Add C.Value, vbNullString       'Keep the first occurrence
                arrMark(C.row - procRng.cells(1).row, 1) = "True"      'place the marker for the first occurrence
            End If
            If C.Value <> "" Then i = i + 1 'for the case of empty cells above the header...
        End If
    Next C
    'place the marker column header, if not already existing:
     If colMark Is Nothing Then sh.cells(procRng.cells(1).row, lastC).Value = markName 'place the marker column name, IF NOT EXISTS
     
    If sh.AutoFilterMode Then sh.AutoFilterMode = False  'eliminate the filter, if any
    
    'drop the markers array content:
    sh.cells(procRng.cells(1).row + 1, lastC).Resize(UBound(arrMark), 1).Value2 = arrMark
    
    'filter by the marker column
    sh.Range(sh.cells(procRng.cells(1).row, 1), sh.cells(sh.UsedRange.rows.count, lastC)).AutoFilter lastC, "True"
End Sub

Answer 2

You can easily create such helper column with a formula. Put the following formula into your helper column. The example assumes that the values that needs to be checked for duplicates are in column A and that your data starts in row 2.

=COUNTIF(A$1:A2,A2)=1

(Note that on my computer the parameter separator is a semicolon)

Update

if you want to write this value via VBA:

If Not dict.Exists(C.value) Then
    dict.Add C.value, vbNullString       ' Keep the first occurrence
    C.Offset(0, 1).value = True          ' Signal: This is the first occurrence
Else
    C.Offset(0, 1).value = False
    (...)

You just need to adapt the Offset so that the data is written into the correct column.