avoid #NV values with excel vba

I have written this piece of code:

thisworkbook.Sheets("test_destination").Range("J2:K" & lastrow_orgid).Value = Application.WorksheetFunction.Unique(diesesworkbook.Sheets("test_source").Range("J2:K" & lastrow_orgid).Value)

It does what it is supposed to do but it creates these #NV values. Maybe someone could tell me if there is another way to write this to avoid these #NV values.

>Solution :

Couple of things.

1. Work with objects. This will make your life easier.
2. You are assuming that the returned range will have the same width as the input range and hence you are getting that error.
3. Store the unique values in an array and then output the array to the relevant range.

Here is an example. Amend as applicable.

Option Explicit

Sub CriarBotaoLaranja()
    Dim ArOutput As Variant
    Dim wsA As Worksheet
    Dim wsB As Worksheet
    Dim lastrow_orgid As Long
    
    '~~> Change the worksheets as applicable
    Set wsA = ThisWorkbook.Sheets("test_destination")
    Set wsB = ThisWorkbook.Sheets("test_source")
    
    '~~> Harcoding this value. change as applicable
    lastrow_orgid = 10
    
    '~~> Get the unique values in an array
    ArOutput = WorksheetFunction.Unique(wsB.Range("J2:K" & lastrow_orgid).Value)
    
    '~~> Output the result to the relevant range
    wsA.Range("J2").Resize(UBound(ArOutput), 2).Value = ArOutput
End Sub

In Action