Unique values in Column

[JoeExcelHelp]

I have the following 1st code that lists unique values in column ZY3 from column ZX
For some reason the code works well but it duplicates the 1st unique value in ZY3 and ZY4.. All renaming values are fine and not duplicated
If it helps, values within column ZX are formula based and the strangely, the first duplicate value in ZY3 contains a formula whereas the duplicate value in ZY4 does not
The 2nd code is me trying to make the 1st code sheet specific :)

Code: Select all

Sub CreateUniqueList()
Dim lastrow As Long

lastrow = Cells(Rows.Count, "ZX").End(xlUp).Row
    
    ActiveSheet.Range("ZX3:ZX" & lastrow).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=ActiveSheet.Range("ZY3"), _
    Unique:=True
     
End Sub

Code:

Code: Select all

Sub CreateUniqueList()
Dim lastrow As Long
Dim wshS As Worksheet
Application.ScreenUpdating = False
    Set wshS = Worksheets("CM_Shift")
    lastrow = wshS.Cells(Rows.Count, "ZX").End(xlUp).Row
    ActiveSheet.Range("ZX3:ZX" & lastrow).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=ActiveSheet.Range("ZY3"), _
    Unique:=True
     
End Sub

Code:

[HansV]

AdvancedFilter treats the top cell of the range (ZX3 in your code) as a column header (field name). Could that be the cause of the problem?

If you want the code to run on another sheet than the active sheet, you have to refer to that sheet consistently, and avoid using ActiveSheet:

Code: Select all

Sub CreateUniqueList()
    Dim lastrow As Long
    Dim wshS As Worksheet
    Application.ScreenUpdating = False
    Set wshS = Worksheets("CM_Shift")
    lastrow = wshS.Cells(wshS.Rows.Count, "ZX").End(xlUp).Row
    wshS.Range("ZX3:ZX" & lastrow).AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=wshS.Range("ZY3"), _
        Unique:=True
    Application.ScreenUpdating = True
End Sub

[JoeExcelHelp]

I forgot to change the other ActiveSheet like i did for LastRow :)

I think it is
The data starts in column ZX3:ZX2500 how could I avoid this?
Also Hans, If I wanted to have this formula do the same thing to AAA (Source) and AAB (Unique Value) within the same sheet can i just add a "Then" after Unique:=True and copy

Code: Select all

lastrow = wshS.Cells(wshS.Rows.Count, "AAA").End(xlUp).Row
    wshS.Range("AAA3:AAA" & lastrow).AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=wshS.Range("AAB3"), _
        Unique:=True

[HansV]

What do you mean by "how could I avoid this"?

If you want to do the same for another column, you have to duplicate the lines

Code: Select all

    lastrow = wshS.Cells(wshS.Rows.Count, "ZX").End(xlUp).Row
    wshS.Range("ZX3:ZX" & lastrow).AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=wshS.Range("ZY3"), _
        Unique:=True

and change the column references in the copy. No "Then".

[JoeExcelHelp]

Avoid the header.. no worries Hans, I could just ignore the first cell with the duplicate

[HansV]

I'd add a header in ZX2 and use wshS.Range("ZX2:ZX" & lastrow)

[JoeExcelHelp]

I get the "1004" the extract range has a missing or illegal field name
I do have a header in ZX2

[HansV]

Copy that header to ZY2 and use wshS.Range("ZY2") as CopyToRange.

[JoeExcelHelp]

The header problem is resolved thanks to you.. Thank you
Unfortunately, I have a new problem with blanks :) attached a WB with some notes

[HansV]

Either move the header from AAA1 to AAA364, or remove the blanks from column AAA before applying AdvancedFilter, or delete AAB2 after doing so.

[JoeExcelHelp]

Thanks Hans.. cant move the header because row data within that column is variable.. Thanks for assistance I could just work around the blank.. It should be consistently blank correct? :)

[HansV]

Yes.