macro valori duplicati

Option Explicit

Sub Copia_No_Duplicati()

    Dim ur As Long

    ur = Range("D" & Rows.Count).End(xlUp).Row
    Range("D3:D" & ur).Copy Destination:=Range("H3")
    Range("H3:H" & ur).RemoveDuplicates Columns:=1, Header:=xlNo

End Sub

Sub Copia_No_Duplicati_3()

    Dim ur As Long      'ultima riga utile
    Dim x As Long       'contatore generico
    
    ur = Range("D" & Rows.Count).End(xlUp).Row
    Range("D3:D" & ur).Copy Destination:=Range("H3")
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For x = ur To 3 Step -1             'retrocedi fino alla riga 3
        If Application.WorksheetFunction.CountIf(Range("H3:H" & x), Range("H" & x)) > 1 Then Range("H" & x).Delete
    Next x
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    
End Sub

Option Explicit

Sub Copia_No_Duplicati()

    Dim ur As Long          'ultima riga utile
    Dim cella As Range      'cella elaborata
    Dim a()                 'matrice
    Dim n As Long           'contatore per matrice
    
    'elabora colonna D
    ur = Sheets("pannelli").Range("D" & Rows.Count).End(xlUp).Row
    ReDim a(1 To ur - 1, 1 To 1)
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For Each cella In Sheets("pannelli").Range("D3:D" & ur)
            If Not .Exists(cella.Value) Then
                n = n + 1
                a(n, 1) = cella.Value
                .Item(cella.Value) = Empty
            End If
        Next
    End With
    Sheets("Foglio2").Range("A3:A" & ur).Value = a

    'elabora colonna M
    ur = Sheets("pannelli").Range("M" & Rows.Count).End(xlUp).Row
    n = 0
    ReDim a(1 To ur - 1, 1 To 1)
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For Each cella In Sheets("pannelli").Range("M3:M" & ur)
            If Not .Exists(cella.Value) Then
                n = n + 1
                a(n, 1) = cella.Value
                .Item(cella.Value) = Empty
            End If
        Next
    End With
    Sheets("Foglio2").Range("B3:B" & ur).Value = a

End Sub