Simple syntax problem

[ValerieT]

Hello


MySheet = ActiveSheet.Name
With Sheets(MySheet)
.Range("C1:C" & lastrow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("D1"), Unique:=True
End With


that copy colum C into colum D, removing duplicate. Works fine

Now I want to copy to an horizontal row but what ever I try
(ex: CopyToRange:=.Range("D1:ZZ1"))
I've got an error 401 with no explanation. Lengh of the range is unknown (as I don't know how duplicates are in the list) so I need a flexible solution (may be the problem is that the range doesn't fit the copied buffer)

Thanks!

[Yongle]

1)Suggest you look compare the two ranges (compare where you "Copy From" with " CopyToRange".
Exit VBA and try a quick copy and paste on the worksheet itself. Select "Copy From" and COPY , then Select "CopyToRange" and PASTE - that will show you if the shapes of the two do not match for Excel.

To copy a column into a row - use paste special transpose

Range D1:ZZ1 looks unusual especially if you are CopyingTO a row - I would usually expect to see the beginning cell of the row only.

[ValerieT]

Thanks Yongle. I turn the issue by doing a mid step (copying first into a column then after transpose to row)

[mancubus]

array alternative to worksheet operations such as filter, copy, etc


Sub Unique_Arr_From_Range()

Dim arr
Dim c As Long

arr = Range("D1:ZZ1").Value
'returns a 2D (1 To 1, 1 To 699) array

With CreateObject("Scripting.Dictionary")
For c = 1 To UBound(arr, 2)
.Item(arr(1, c)) = "this_can_be_anything"
Next
'Example:
Range("A1:A" & .Count) = Application.Transpose(.keys)
End With
End Sub


more on scripting dictionary at http://www.snb-vba.eu/VBA_Dictionary_en.html