Subscribe

RSS Feed (xml)

Powered By

Skin Design:
Free Blogger Skins

Powered by Blogger

sub zero

Kamis, 10 Juni 2010

macro excel vba utk sortir data

inilah rumus untuk sortir data di excel dengan macro sekaligus bikin nomor urut :


sort dengan macro
dan tambah rumus +1
Sub sortirrrssnnnnn()
'
' sortirrrssnnnnn Macro
'

'
Range("A3:G123").Select
ActiveWorkbook.Worksheets("REF").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("REF").Sort.SortFields.Add Key:=Range("B4:B123"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("REF").Sort.SortFields.Add Key:=Range("C4:C123"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("REF").Sort
.SetRange Range("A3:G123")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("G4").Select
ActiveCell.FormulaR1C1 = "=1"
Range("G5").Select
ActiveCell.FormulaR1C1 = "=R[-1]C+1"
Range("G5").Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("G12").Select
End Sub