вот что написал макрорекордер
[vba]Код
Sub Макрос1()
Dim tt_&, dd_&, ii_&
tt_ = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
Range("D1:D" & tt_).Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
Application.CutCopyMode = False
ii_ = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
ActiveSheet.Range("$A$1:$A$" & ii_).RemoveDuplicates Columns:=1, Header:=xlNo
Columns("A:A").AutoFilter
ActiveSheet.Range("$A$1:$A$" & ii_).AutoFilter Field:=1, Criteria1:="<>*@*", _
Operator:=xlAnd
dd_ = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Rows("1:" & dd_).Delete Shift:=xlUp
End Sub
[/vba]