在VBA中編寫排序代碼--Sort方法

看看下面的Excel界面截圖，「排序」和「篩選」往往在一起，這大概是很多數據需要先排序後篩選吧！

在Excel 2007中新增了Sort對象，在錄製宏時Excel會自動用到這個對象，但我們今天不會講解這個對象，待以後再詳解。今天主要講解Range對象的Sort方法，對於3個以內的欄位排序很方便。其語法如下：

Range對象.Sort(Key1,Order1 As XlSortOrder, _

Key2,Type,Order2As XlSortOrder, _

Key3,Order3As XlSortOrder, _

HeaderAs XlYesNoGuess, _

OrderCustom,MatchCase,_

OrientationAs XlSortOrientation, _

SortMethodAs XlSortMethod, _

DataOption1As XlSortDataOption, _

DataOption2As XlSortDataOption, _

DataOption3As XlSortDataOption)

說明：

l 所有參數均可選。

l 參數Key1、Key2、Key3指定排序欄位，確定要排序的值，但參數Key2、Key3不能用於排序數據透視表。

l 參數Order1、Order2、Order3，分別確定參數Key1、Key2、Key3指定值的排序順序，相應的常量值是xlDescending或者xlAscending（默認）。

l 參數Type，指定要排序的元素。僅用於數據透視表，可以指定為xlSortLabels或者xlSortValues。

l 參數Header，指定是否第一行包含標題信息，默認為xlNo。如果想要Excel嘗試確定標題，那麼指定其值為xlGuess。

l 參數OrderCustom，指定一個基於1的整數偏移量到自定義排序順序列表，使用自定義的排序順序進行排序。

l 參數MatchCase，設置為True執行區分大小寫的排序，為False則執行不區分大小寫的排序，不能用於數據透視表。

l 參數Orientation，默認按行進行排序且數據是垂直排列。如果數據是水平排列的，通過指定該參數使其按列進行排序。相應的常量值是xlSortRows或者xlSortColumn。

l 參數SortMethod，指定排序方法，適用於除英語以外的語言。

l 參數DataOption，有3個參數，用來指定排序時對單元格中文本和數字的處理。如果指定其值為xlSortTextAsNumbers，將文本當作數據進行排序，默認值是xlSortNormal，分別對數字和文本數據排序。不能應用於數據透視表排序。

參數DataOption1用於指定如何排序在Key1中指定的單元格區域中的文本。

參數DataOption2，用於指定如何排序在Key2中指定的單元格區域中的文本。

參數DataOption3，用於指定如何排序在Key3中指定的單元格區域中的文本。

下面以下圖所示的工作表來演示，以理解Sort方法及其參數。主要是介紹前面幾個參數，其它的參數將會在以後的文章中涉及時再進行相應講解。

首先以「性別」作為排序欄位，升序排列，並且第一行作為標題信息，代碼如下：

Sub testSort1()

Dim rng As Range

設置要排序的區域

Set rng = Range("A1:G10")

排序

rng.Sort Key1:="性別",Order1:=xlAscending, Header:=xlYes

End Sub

運行代碼後的結果如下圖：

接下來，再添加排序欄位：以「性別」作為第1排序欄位升序排列，以「總分」作為第2排序欄位降序排列。代碼如下：

Sub testSort2()

Dim rng As Range

設置要排序的區域

Set rng = Range("A1:G10")

排序

rng.Sort Key1:="性別",Order1:=xlAscending, _

Key2:="總分",Order2:=xlDescending, _

Header:=xlYes

End Sub

Excel將會以「性別」作為主要關鍵字升序排列，以「總分」作為次要關鍵字降序排列，即主關鍵字排序相同的，再以次關鍵字排序。結果如下圖所示：

示例1：查找滿足某項條件的所有數據並按順序排列

仍以上面的工作表為例，我們需要所有男同學的成績並以總分從高到低的順序排列。將排序與自動篩選結合，可達到我們的目的。

代碼如下：

Sub testSort3()

Dim rng As Range

設置要排序的區域

Set rng = Range("A1:G10")

排序

rng.Sort Key1:="性別",Order1:=xlAscending, _

Key2:="總分", Order2:=xlDescending,_

Header:=xlYes

篩選

rng.AutoFilter Field:=3, Criteria1:="男"

End Sub

運行代碼後的效果如下圖：

示例2：查找滿足某項條件的不重複數據

如本文開頭所示的工作表，要求獲取男女同學中總分最高的同學數據記錄。將排序與高級篩選相結合，可以達到我們的目的。

Sub testSort4()

Dim rng As Range

設置要排序的區域

Set rng = Range("A1:G10")

排序

rng.Sort Key1:="性別",Order1:=xlAscending, _

Key2:="總分",Order2:=xlDescending, _

Header:=xlYes

篩選

rng.Columns(3).AdvancedFilterAction:=xlFilterInPlace, _

Unique:=True

End Sub

代碼中的：

rng.Columns(3)

表示單元格區域rng中的第3列，即「性別」欄位列。

運行代碼後的效果如下圖：

示例3：雙擊列標題自動排序

在本文的示例工作表中，雙擊列標題，會升序排列該標題下的內容，再次雙擊該列標題，降序排列。代碼如下：

聲明變數,用於存儲升序降序值及排序列號

Dim iDirection As Integer

Dim iColumn As Integer

Private SubWorksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim rng As Range

設置排序的單元格區域

Set rng =Range("A1").CurrentRegion

限定在前8列第1行

If Target.Column < 8 And Target.Row = 1Then

If Target.Column <> iColumn Then

iColumn = Target.Column

默認設置為升序排列

iDirection = xlAscending

Else

在升序與降序之間切換

If iDirection = xlAscending Then

iDirection = xlDescending

Else

iDirection = xlAscending

End If

End If

排序

rng.Sort Key1:=rng.Cells(1, iColumn), _

Order1:=iDirection, _

Header:=xlYes

End If

End Sub

說明：

l 代碼位於工作表模塊的Worksheet_BeforeDoubleClick事件中，在工作表單元格中雙擊滑鼠時發生該事件。（關於工作表事件，將在本系列文章後面的Worksheet對象系列中詳細講解）

l 在模塊頂部子過程外面聲明變數，表明該變數可用於該模塊下所有的子過程。本程序代碼之所以在模塊頂部聲明變數，是為了保存雙擊事件發生前變數的值，以便與雙擊事件發生後相關值比較，從而實現升序和降序的切換。（關於變數作用範圍，將在本系列文章後面詳細講解）

l Range("A1").CurrentRegion獲取單元格A1所在的區域，可參閱《ExcelVBA解讀（38）：快速確定自已的地盤——CurrentRegion屬性》。

示例4：根據活動單元格排序

在上文所示的工作表中，當單元格在A1:G10區域內移動時，將根據活動單元格所處的位置對其所在列按降序排序。代碼如下：

Private SubWorksheet_SelectionChange(ByVal Target As Range)

Dim rng As Range

Set rng = Range("A1:G10")

將範圍限定在列A至列G和1至10行

If Target.Column < 8 And Target.Row <11 Then

rng.Sort Key1:=ActiveCell,Order1:=xlDescending, Header:=xlYes

End If

End Sub

說明：

l 代碼位於工作表模塊的Worksheet_SelectionChange事件中，當活動單元格發生變化時觸發該事件。（關於工作表事件，將在本系列文章後面的Worksheet對象系列中詳細講解）

示例5：根據顏色排序

這是Excel 2013 VBA幫助文檔中Sort方法的示例，按單元格的背景色進行排序。示例代碼如下：

Sub SortbyColor()

Dim lngLastRow As Long

Dim i As Long

列A中最後一個單元格

lngLastRow = Range("A" &Rows.Count).End(xlUp).Row

遍歷列A中的單元格並將其背景色索引值放置在列C中相應單元格

For i = 2 To lngLastRow

Cells(i, 3) = Cells(i,1).Interior.ColorIndex

Next i

基於列C中的數據排序

Range("C1") = "索引值"

Columns("A:C").SortKey1:=Range("C1"), _

Order1:=xlAscending, Header:=xlYes

清除列C中用於排序的臨時值

Columns(3).ClearContents

End Sub

說明：

l 代碼中，首先使用ColorIndex屬性獲取列A中單元格顏色索引值，並將這些值存儲在列C中的相應行，然後對列C排序，從而達到對列A按顏色排序的效果。

l ClearContents方法用於清除單元格中的內容。

l 代碼運行的過程及結果如下圖所示：

示例6：排序有部分相同數據的行

如下圖所示，課程的組合有3種，分別是「語文、數學、英語」，「數學、體育、歷史」，「體育、化學、生物」，但上課的時間不同，要求將相同組合的課程排在一起。

代碼如下：

Sub SortSameData()

Dim rng As Range

Dim str As String

Dim i As Long, j As Long

Set rng = Range("A1:D10")

提取課程組合併放置在排序輔助列

For i = 2 To rng.Rows.Count

str = ""

For j = 2 To rng.Columns.Count

str = str & Cells(i, j)

Next j

Cells(i, j) = str

Next i

設置排序數據區域並按課程組合排序

Set rng = rng.Resize(, 5)

rng.Sort Key1:=rng.Columns(5),Order1:=xlDescending, Header:=xlYes

清除輔助列內容

rng.Columns(5).ClearContents

End Sub

說明：

l 技巧：將多列組合成一列，並將該列作為排序列，從而達到相同數據排序在一起的目的。

示例7：自定義排序

如下圖所示，我們想按單元格區域I1:I5中的順序對單元格區域A1:G10進行排序。也就是說，無論數據如何變化，在單元格區域I1:I5中的5名同學都是按照這樣的順序排列。

代碼如下：

Sub CustomSort()

Dim iListNum As Integer

添加自定義列表

Application.AddCustomListListArray:=Range("I1:I5")

獲取列表編號

iListNum =Application.GetCustomListNum(Range("I1:I5").Value)

使用自定義列表排序

注意,應使用iListNum+1作為參數OrderCustom的值

『指定自定義列表(參見OrderCustom參數說明)

Range("A1:G10").Sort Key1:=Range("B1"),Order1:=xlAscending, _

Header:=xlYes,OrderCustom:=iListNum + 1

移除自定義列表,以便於再次運行代碼

Application.DeleteCustomList iListNum

End Sub

說明：

l 這段程序代碼中有3個我們以前沒有見過的方法，即Application對象的AddCustomList方法、GetCustomListNum方法、DeleteCustomList方法。與排序相匹配使用的。（註：也與自動填充相匹配）

l AddCustomList方法的語法如下：

Application對象.AddCustomList(ListArray,ByRow)

添加自定義列表，用於自定義自動填充或自定義排序。其中，參數ListArray必需，指定自定義排序數據，可以是字元串數組或者Range對象。參數ByRow可選，僅用於當參數ListArray是Range對象時；設置為True時從單元格區域中的行創建自定義列表，設置為False時從單元格區域的列創建自定義列表；如果忽略該參數且單元格區域中的列比行多，那麼將從單元格區域行創建自定義列表。

注意，如果試圖添加的列表已存在，那麼該方法不會執行任何操作，會報出錯消息。

l GetCustomListNum方法的語法如下：

Application對象.GetCustomListNum(ListArray)

返回字元串數組的自定義列表編號，可以用於匹配內置列表和自定義列表。其中，參數ListArray必需，指定字元串數組。

注意，如果沒有相應的列表，那麼該方法將導致錯誤。

l DeleteCustomList方法的語法如下：

Application對象.DeleteCustomList(ListNum)

刪除自定義列表。其中，參數ListNum必需，指定自定義列表編號。編號必須大於或等於5，因為Excel有4個內置的不可刪除的自定義列表。

注意，如果列表編號小於5或者沒有相匹配的自定義列表，那麼該方法將導致錯誤。（這是Excel 2013幫助文檔中的說明，實際上Excel 2007中就有11個內置的不可刪除的列表。）

運行代碼後的結果如下圖：

本文首發於完美Excel微信號：excelperfect

原文名《ExcelVBA解讀（54）：排序——Sort方法》，轉載請註明來源。

推薦閱讀：