Copy Remove Duplicates and Transpose VBA Code Error

[Dokat]

Hi,

I have below code where i am trying copy row A:A remove duplicates, transpose and paste it to B2. However i am getting Run time error '1004': Application defined or Object defined error message.

Can someone please help me with the issue?

Sub copytranspose()
Range("A" & Rows.Count).Copy
ActiveSheet.Range("A" & Rows.Count).RemoveDuplicates Columns:=Array(1), _
Header:=xlYes
ActiveSheet.Range(“B2”).PasteSpecial , Transpose:=True
End Sub

 

[AlanSidman]

I think you may want to remove your duplicates before you try to copy.

 

[Dokat]

I dont want to delete the original order in the columns thats why i dont want to remove duplicates before copying.

Thanks

 

[AlanSidman]

I don't think what you want will happen. Maybe someone else has a better idea, but IMO I don't see it.

 

[vletm]

Dokat
From Your the 1st line ...

Range("A" & Rows.Count).Copy

What would hope that this do?
Have You tried for testing purpose eg

Range("A" & Rows.Count).Select

Would it select range as You hope?
... and try to continue testing as above row-by-row.

 

[Ajesh]

Try this:

Sub copytranspose()
Dim Dict As Object
Dim Arr As Variant
Dim i As Long
Dim LastRow As Long

Set Dict = CreateObject("Scripting.Dictionary")
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Arr = Range("A1:A" & LastRow)
For i = 1 To UBound(Arr, 1)
    Dict(Arr(i, 1)) = 1
Next i

ActiveSheet.Range("B2").Resize(1, _
             UBound(Application.Transpose(Dict.keys))) = Dict.keys
End Sub

 

[Dokat]

It worked. Thank you!