excel双击列标题自动排序的VBA代码

10/11/2015来源:Excel教程人气:1839

  通过以下的VBA代码,将其放到工作表的双击事件中,就能取到双击工作表的列标题(双击列的名字),当前列自动进行排序。

  比如,将代码放在如下的事件中:

  PRivate Sub Worksheet_SelectionChange(ByVal Target As Range)

  End Sub

  代码如下:

  Dim YouRg As Range

  If Target.Column <= Me.Cells (1,1).CurrentRegion.Columns.Count And Target.Row = 1 Then

  If Target.Column <> mnColumn Then

  mnColumn = Target.Column

  mnDirection = xlAscending

  Else

  If mnDirection = xlAscending Then

  mnDirection = xlDescending

  Else

  mnDirection = xlAscending

  End If

  End If

  Set YouRg = Me.Cells(1, 1).CurrentRegion

  YouRg .Sort key1:=YouRg .Cells(1, mnColumn), order1:=mnDirection,header:=xlYes

  Set YouRg = Nothing

  Cancel = True

  End If

  以上代码是升序排序,你可以改为降序排序。