excel自動(dòng)排序vba代碼

《excel自動(dòng)排序vba代碼》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在應(yīng)用文檔-天天文庫(kù)。

1、Sub單列自動(dòng)排序()'a為需要排序的數(shù)組，up為True則為升序排列，為False，則為降序排列。DimupAsBooleanDimranktypeAsStringDimiAsInteger,jAsIntegerDimtempAsDouble,temp1AsIntegerDima(1000,3)AsDouble'a數(shù)組定義大小要超過(guò)排序數(shù)，此處是按排序數(shù)在1000個(gè)以內(nèi)定義'*********************colnum=1'排序數(shù)據(jù)列rankcolnum=2'排序碼列startrow=5'排序數(shù)據(jù)起始行號(hào)

2、endrow=135'排序數(shù)據(jù)末尾行號(hào)ranktype="降序"'排序方式，“升序”或“降序”Ifranktype="升序"Thenup=TrueElseup=FalseEndIfFori=1Toendrow-startrow+1a(i,0)=Val(ActiveSheet.Cells(startrow+i-1,colnum))a(i,1)=startrow+i-1a(i,2)=0Next'*********************Fori=1Toendrow-startrow'進(jìn)行n-1輪比較Forj=endrow

3、-startrow+1Toi+1Step-1'從n到i個(gè)元素兩兩進(jìn)行比較IfupThen'判斷升降序Ifa(j,0)a(j-1,0)Thentemp=a(j,0)temp1=a(j,1)a(j,0)=a(j-1,0)a(j,1)=a(j-1,1)a(j-1,0)=tempa(j-1,1)=temp1

4、EndIfEndIfNextjNextiFori=1Toendrow-startrow+1a(i,2)=iNextFori=2Toendrow-startrow+1Ifa(i,0)=a(i-1,0)Thena(i,2)=a(i-1,2)EndIfNextFori=1Toendrow-startrow+1ActiveSheet.Cells(a(i,1),rankcolnum)=a(i,2)NextEndSub'數(shù)據(jù)自動(dòng)排序(單列間隔)Sub自動(dòng)排序1()'a為需要排序的數(shù)組，up為True則為升序排列，為False，則

5、為降序排列。DimupAsBooleanup=FalseDimiAsInteger,jAsIntegerDimtempAsDouble,temp1AsIntegerForcolnum=1To9'9為需要排序的數(shù)據(jù)列數(shù)Dima(100,3)AsDouble'*********************startrow=3'排序數(shù)據(jù)起始行號(hào)endrow=20'排序數(shù)據(jù)末尾行號(hào)Fori=1Toendrow-startrow+1a(i,0)=Val(ActiveSheet.Cells(startrow+i-1,(colnum-

6、1)*2+4))a(i,1)=startrow+i-1a(i,2)=0Next'*********************Fori=1Toendrow-startrow'進(jìn)行n-1輪比較Forj=endrow-startrow+1Toi+1Step-1'從n到i個(gè)元素兩兩進(jìn)行比較IfupThen'判斷升降序Ifa(j,0)

7、lseIfa(j,0)>a(j-1,0)Thentemp=a(j,0)temp1=a(j,1)a(j,0)=a(j-1,0)a(j,1)=a(j-1,1)a(j-1,0)=tempa(j-1,1)=temp1EndIfEndIfNextjNextiFori=1Toendrow-startrow+1a(i,2)=iNextFori=2Toendrow-startrow+1Ifa(i,0)=a(i-1,0)Thena(i,2)=a(i-1,2)EndIfNextFori=1Toendrow-startrow+1Activ

8、eSheet.Cells(a(i,1),(colnum-1)*2+5)=a(i,2)NextNextEndSub