Snasui Excel Forum

Code: Select all

Sub removenameDup()
    Sheets("ยอดบัญชี").Select
    Columns("A:A").Select
    Selection.Copy
    Columns("E:E").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("E1").Select
    Range(Selection, Selection.End(xlDown)).Select
    myrange1 = ActiveCell.Address
    ActiveSheet.Range(myrange1).RemoveDuplicates Columns:=1, Header:=xlNo
    ActiveWorkbook.Worksheets("ยอดบัญชี").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("ยอดบัญชี").Sort.SortFields.Add Key:=Range( _
        myrange1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("ยอดบัญชี").Sort
        .SetRange Range(myrange1)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Code: Select all

Public Sub Test()
    Dim r As Range
    With Sheets(1)
        .Activate
        .Range("e:e").Clear
        Set r = .Range("a1:a" & .Range("a" & .Rows.Count).End(xlUp).Row)
        r.Offset(, 4).Value = r.Value
        r.Offset(, 4).RemoveDuplicates Columns:=1, Header:=xlNo
        r.Offset(, 4).Sort key1:=.Range("e1") _
            , order1:=xlAscending, Header:=xlNo
    End With
End Sub