在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方法及其参数。主要是介绍前面几个参数,其它的参数将会在以后的文章中涉及时再进行相应讲解。

示例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 Sub

Worksheet_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在模块顶部子过程外面声明变量,表明该变量可用于该模块下所有的子过程。本程序代码之所以在模块顶部声明变量,是为了保存双击事件发生前变量的值,以便与双击事件发生后相关值比较,从而实现升序和降序的切换。(关于变量作用范围,将在本系列文章后面详细讲解)

lRange("A1").CurrentRegion获取单元格A1所在的区域,可参阅《Excel

VBA解读(38):快速确定自已的地盘——CurrentRegion属性》。

示例4:根据活动单元格排序

在上文所示的工作表中,当单元格在A1:G10区域内移动时,将根据活动单元格所处的位置对其所在列按降序排序。代码如下:

Private Sub

Worksheet_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代码运行的过程及结果如下图所示:

代码如下:

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方法。与排序相匹配使用的。(注:也与自动填充相匹配)

lAddCustomList方法的语法如下:

Application 对象 . AddCustomList (ListArray,ByRow)

添加自定义列表,用于自定义自动填充或自定义排序。其中,参数ListArray必需,指定自定义排序数据,可以是字符串数组或者Range对象。参数ByRow可选,仅用于当参数ListArray是Range对象时;设置为True时从单元格区域中的行创建自定义列表,设置为False时从单元格区域的列创建自定义列表;如果忽略该参数且单元格区域中的列比行多,那么将从单元格区域行创建自定义列表。

注意,如果试图添加的列表已存在,那么该方法不会执行任何操作,会报出错消息。

lGetCustomListNum方法的语法如下:

Application 对象. GetCustomListNum (ListArray)

返回字符串数组的自定义列表编号,可以用于匹配内置列表和自定义列表。其中,参数ListArray必需,指定字符串数组。

注意,如果没有相应的列表,那么该方法将导致错误。

lDeleteCustomList方法的语法如下:

Application 对象 . DeleteCustomList (ListNum)

删除自定义列表。其中,参数ListNum必需,指定自定义列表编号。编号必须大于或等于5,因为Excel有4个内置的不可删除的自定义列表。

注意,如果列表编号小于5或者没有相匹配的自定义列表,那么该方法将导致错误。(这是Excel 2013帮助文档中的说明,实际上Excel 2007中就有11个内置的不可删除的列表。)

运行代码后的结果如下图: