Please help with using VBA to copy value except duplicates and blank to a specific area

[nikan93]

Hi all,

Please help me with the VBA code to copy value in the range of C1 to the cell before the first blank cell from workbook A, removed duplicates and blanks, to a specific area starting at F3 in workbook B. Please help to note that we just know the beginning range and don't know the last range of the above "specific area"

Thank you so much for your help.

 

[onlyadrafter]

Hello,

does this work as expected?

Sub Macro2()
    Range("C1").Select
    Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("I1" _
        ), Unique:=True
    Range("I1:I" & Range("I" & Rows.Count).End(xlUp).Row).Copy
    Windows("Book3").Activate
    Range("F3").PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False
    Windows("Book2").Activate
    Columns("I:I").ClearContents
End Sub

you will need to change workbook names.

It also assumes both workbooks are open, and column I is available in the origin sheet.

 

[nikan93]

Hi onlyadrafter,

There was an error notification like this: "The command could not be completed by using the range specified" and the below line is highlighted when I chose "Debug":

Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("I1" _        ), Unique:=True

 

[onlyadrafter]

Hello,

can't replicate the error. What data do you have in Column C?

Sub Macro2()
    Range("C1").Select
    Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=Range("I1"), Unique:=True
    Range("I1:I" & Range("I" & Rows.Count).End(xlUp).Row).Copy
    Windows("Book3").Activate
    Range("F3").PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False
    Range("F3:F" & Range("F" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
    Windows("Book1").Activate
    Columns("I:I").ClearContents
End Sub

this code will clear duplicate blanks, didn't spot that earlier.