VBA常用技巧之第2章 Worksheet（工作表）对象

  
技巧1  引用工作表的方式

VBA中，在不同的工作表之间转换或者对不同工作表中的单元格区域进行操作时，需要指定引用的工作表，通常有下面几种方法：

1-1  使用工作表的名称

工作表名称是指显示在工作表标签中的文本，工作表名称可以使用WorkSheets集合和Sheets集合两种引用方式，如下面的代码所示。

#001  Sub ShActivate()

#002      Worksheets("索引号").Activate

#003      'Sheets("索引号").Activate

#004  End Sub

第3、4行代码都激活工作簿中名称为“索引号”的工作表，激活后“索引号”工作表将成为活动工作表。

WorkSheets集合包含所有的工作表，而Sheets集合不仅包含工作表集合WorkSheets，还包含图表集合Charts、宏表集合Excel4MacroSheets与MS Excel 5.0对话框集合DialogSheets等。

任何时刻工作簿中只有一个工作表是活动工作表。

1-2  使用工作表的索引号

工作表索引号是指工作表在工作簿中的位置，Excel根据工作表在工作表标签中的位置以1开始从左向右进行编号。下面的代码选中并激活当前工作簿中第1个工作表：

#001  Sub ShIndex()

#002      Worksheets(1).Select

#003  End Sub

单个WorkSheet对象的Select方法与Activate方法的主要区别在于Select方法要求工作表可视。

注意 当工作簿包括工作表、宏表、图表等时，使用索引号引用工作表如Sheets(1)与WorkSheets(1)引用的可能不是同一个表。

使用Worksheet对象的Index属性可以返回工作表的索引号，如下面的代码所示。

#001  Sub ShInde()

#002      MsgBox Worksheets("索引号").Index

#003  End Sub

1-3  使用工作表的代码名称

使用Worksheet对象的CodeName属性可以返回工作表的代码名称，如下面的代码所示。

#001  Sub ShCodeName()

#002      MsgBox Sheets(1).CodeName

#003  End Sub

工作表的代码名称显示在VBE工程资源管理器窗口中，在属性窗口中能够修改工作表代码名称，如图 21?1所示。在VBA中能够直接使用工作表的代码名称引用工作表，即使工作表的名称被修改，代码仍然能够正常运行。

图 21?1   工作表的代码名称

1-4  使用ActiveSheet属性引用活动工作表

使用ActiveSheet属性可以返回活动工作表，如下面的代码所示。

#001  Sub ShActive()

#002      MsgBox ActiveSheet.Name

#003  End Sub

ActiveSheet属性应用于AppActivate对象、Window对象和Workbook对象时，如果未给出对象识别符，返回活动工作簿中的活动工作表。

技巧2              选择工作表的方法

在VBA中需要激活或者选择某个工作表时使用Select方法或Activate方法，如下面的代码所示。

#001  Sub SelectSh()

#002      Worksheets("Sheet2").Select

#003  End Sub

#004  Sub ActivateSh()

#005      Worksheets("Sheet2").Activate

#006  End Sub

代码解析：

SelectSh过程使用Select方法选择“Sheet2”工作表，而ActivateSh过程则使用Activate方法选择“Sheet2”工作表，从表面看两者的作用是相同的，但是如果“Sheet2”工作表是隐藏的，Activate方法可以正常运行，而Select方法将会出现错误，如图 22?1所示。

图 22?1   Select方法无效提示

如果需要同时选中工作簿中的所有工作表，则只能使用Select方法而不能使用Activate方法，如下面的代码所示。

#001  Sub SelectShs()

#002      Dim Shs As Worksheet

#003      For Each Shs In Worksheets

#004          Shs.Select False

#005      Next

#006  End Sub

#007  Sub SelectSheets()

#008      Worksheets.Select

#009  End Sub

#010  Sub ArraySheets()

#011      Worksheets(Array(1, 2, 3)).Select

#012  End Sub

代码解析：

    SelectShs过程遍历工作表并使用带参数的Select方法选中所有工作表。应用于Worksheet对象的Select方法的语法如下：

Select(Replace)

参数Replace是可选的。如果该值为True，则用指定对象替代当前选定对象。如果该值为False，则延伸当前选定对象以包括任何以前选定的对象。

SelectSheets过程使用Worksheets集合的Select方法选中集合中所有的对象。

ArraySheets过程使用Array 函数返回工作簿中的前三张工作表并使用Worksheets集合的Select方法选中前三张工作表。

技巧3               遍历工作表的方法

在Excel应用中经常需要遍历工作簿中所有的工作表，有以下两种方法可以实现。

3-1  使用For...Next 语句

使用For...Next 语句遍历工作簿中所有的工作表，如下面的代码所示。

#001  Sub ShCount1()

#002      Dim c As Integer

#003      Dim i As Integer

#004      Dim s As String

#005      c = Worksheets.Count

#006      For i = 1 To c

#007          s = s & Worksheets(i).Name & Chr(13)

#008      Next

#009      MsgBox "工作簿中含有以下工作表:" & Chr(13) & s

#010  End Sub

代码解析：

ShCount1过程使用For...Next 语句遍历工作簿中所有的工作表，并用消息框显示所有的工作表名称。
       第5行代码根据Worksheet对象的Count属性返回工作簿中工作表的数量赋给变量c。应用于Worksheet对象的Count属性返回Worksheets集合中工作表的数量，语法如下：

expression.Count

第6行代码开始For...Next 语句循环。For...Next 语句以指定次数来重复执行一组语句，语法如下：

For counter = start To end [Step step]

    [statements]

    [Exit For]

    [statements]

Next [counter]

参数counter是必需的，用做循环计数器的数值变量。

参数start是必需的，循环计数器的初值。
      参数end是必需的，循环计数器的终值。
      参数step是可选的，环计数器的步长，缺省值为 1。
      参数statements是可选的，放在For和Next之间的一条或多条语句，它们将被执行指定的次数。
      第7行代码在For...Next循环中根据工作表的索引号取得所有工作表的名称赋给字符串变量s。

运行ShCount过程结果如图 23?1所示。

图 23?1   取得所有工作表名称

3-2  使用For Each...Next 语句

使用For Each...Next语句遍历工作簿中所有的工作表，如下面的代码所示。

#001  Sub ShCount2()

#002      Dim Sh As Worksheet

#003      Dim s As String

#004      For Each Sh In Worksheets

#005          s = s & Sh.Name & Chr(13)

#006      Next

#007      MsgBox "工作簿中含有以下工作表:" & Chr(13) & s

#008  End Sub

代码解析：

ShCount2过程使用For Each...Next语句遍历工作簿中所有的工作表，并用消息框显示所有工作表名称。

第4行代码使用For Each...Next语句遍历Worksheets集合中所有元素。For Each...Next语句针对一个数组或集合中的每个元素，重复执行一组语句，语法如下：

For Each element In Group

    [statements]

    [Exit For]

    [statements]

Next [element]

参数element是必需的，用来遍历集合或数组中所有元素的变量。

参数group是必需的，对象集合或数组的名称。

参数statements是可选的，针对对象集合或数组中的每一项执行的一条或多条语句。

第5行代码将返回的工作表的名称赋给字符串变量s。

运行ShCount2过程结果如图 23?1所示。

技巧4              在工作表中上下翻页

如果需要在工作簿的工作表中进行上下翻页，可以使用下面的代码。

#001  Sub DownSheet()

#002      Dim i As Integer

#003      i = Worksheets.Count

#004      If ActiveSheet.Index < i Then

#005          Worksheets(ActiveSheet.Index + 1).Activate

#006      Else

#007          Worksheets(1).Activate

#008      End If

#009  End Sub

#010  Sub UpSheet()

#011      Dim i As Integer

#012      i = Worksheets.Count

#013      If ActiveSheet.Index > 1 Then

#014          Worksheets(ActiveSheet.Index - 1).Activate

#015      Else

#016          Worksheets(i).Activate

#017      End If

#018  End Sub

代码解析：

DownSheet过程向下翻页，第3、12行代码使用Worksheets对象的Count属性取得工作表的数目，第4行到第7行代码根据Index属性判断活动工作表是否是工作簿中的最后一张工作表。如果活动工作表不是最后一张工作表则激活活动工作表的下一张工作表，否则激活第一张工作表。

UpSheet过程向上翻页，第13行到第16行代码根据Index属性判断活动工作表是否是工作簿中的第一张工作表。如果活动工作表不是第一张工作表则激活活动工作表的上一张工作表，否则激活最后一张工作表。

技巧5               工作表的添加与删除

在工作簿中添加工作表使用Add方法，如下面的代码所示。

#001  Sub Addsh()

#002      Dim Sh As Worksheet

#003      With Worksheets

#004          Set Sh = .Add(after:=Worksheets(.Count))

#005          Sh.Name = "数据"

#006      End With

#007  End Sub

代码解析：

Addsh过程使用Add方法在工作簿中新建“数据”工作表。

第2行代码声明变量Sh为工作表对象。

第4行行代码使用Add方法在工作簿的最后新建“数据”工作表。

Add 方法应用于Sheets和Worksheets对象时新建工作表、图表或宏表，语法如下：

expression.Add(Before, After, Count, Type)

参数Before是可选的，指定工作表对象，新建的工作表将置于此工作表之前。

参数After是可选的，指定工作表对象，新建的工作表将置于此工作表之后。

如果Before和 After两者均省略，则新建的工作表将插入到活动工作表之前。

参数Count可选，要新建的工作表的数目。默认值为 1。

参数Type可选，指定新建的工作表类型。

第5行代码将添加的工作表重命名为“数据”。

如果需要在工作簿中批量添加工作表，可以使用下面的代码。

#001  Sub Addsh_2()

#002      Dim i As Integer

#003      Dim sh As Worksheet

#004      For i = 1 To 10

#005          Set sh = Sheets.Add(after:=Sheets(Sheets.Count))

#006          sh.Name = i

#007      Next

#008  End Sub

代码解析：

Addsh_2过程使用For...Next 语句和Add方法在工作簿中添加10张工作表并将添加的工作表依次重命名。

在使用以上代码往工作簿中添加工作表时，如果工作簿中已存在相同名称的工作表，运行时会发生错误，代码中断，如图 25?1所示。

图 25?1   运行错误提示

为了避免此错误的发生，可以在添加前先删除所有的工作表，如下面的代码所示。

#001  Sub Delsh()

#002      Dim sh As Worksheet

#003      For Each sh In ThisWorkbook.Sheets

#004          If sh.Name <> "工作表的添加与删除" Then

#005              Application.DisplayAlerts = False

#006              sh.Delete

#007              Application.DisplayAlerts = True

#008          End If

#009      Next

#010  End Sub

代码解析：

Delsh过程使用Delete方法删除工作簿中除了“工作表的添加与删除”工作表以外所有的工作表。

第3行代码使用For Each...Next语句遍历代码所在工作簿中所有的工作表。

第4行到第7行代码判断工作表名称是否为“工作表的添加与删除”,如果不是则使用Delete方法删除。其中第5行代码将Application对象的DisplayAlerts属性设置为False，使删除时不显示如图 25?2所示系统警告对话框。

图 25?2   系统警告对话框

第6行代码使用Delete方法删除工作表，应用于工作表对象的Delete方法删除指定的对象，语法如下：

expression.Delete

参数expression是必需的，该表达式返回“应用于”列表中的对象之一。

在运行添加工作表代码前先删除工作簿中的工作表虽然可以避免同名错误,但也可能误删除有用的工作表,因此更为严谨的方法是在添加前先判断工作簿中是否存在相同名称的工作表,然后再进行下一步的操作。

对于单张工作表可以使用下面的代码。

#001  Sub Addsh_3()

#002      Dim Sh As Worksheet

#003      For Each Sh In Worksheets

#004          If Sh.Name = "数据" Then

#005              MsgBox "工作簿中已有""数据""工作表,不能重复添加!"

#006              Exit Sub

#007          End If

#008      Next

#009      With Worksheets

#010          Set Sh = .Add(after:=Worksheets(.Count))

#011          Sh.Name = "数据"

#012      End With

#013  End Sub

代码解析：

Addsh_3过程在使用Add方法在工作簿中新建“数据”工作表时首先判断工作簿中是否存在“数据”工作表，如果已存在“数据”工作表则不运行添加工作表的代码而只显示一个消息框进行提示，如图 25?3所示。

图 25?3   工作表同名提示

还可以使用错误处理语句来绕过错误，如下面的代码所示。

#001  Sub Addsh_4()

#002      Dim sh As Worksheet

#003      On Error GoTo line

#004      With Worksheets

#005          Set sh = .Add(after:=Worksheets(.Count))

#006          sh.Name = "数据"

#007      End With

#008      Exit Sub

#009  line:

#010      MsgBox "工作簿中已有""数据""工作表,不能重复添加!"

#011      Application.DisplayAlerts = False

#012      Worksheets(Worksheets.Count).Delete

#013      Application.DisplayAlerts = True

#014  End Sub

代码解析：

Addsh_4过程是先使用Add方法在工作簿中新建“数据”工作表，如果工作簿中已存在同名的工作表则使用GoTo语句转移到指定的line行处进行提示并删除已添加还没有重命名的工作表，也就是工作簿中最后一张工作表。

如果是批量添加工作表，使用上述方法时，添加工作表和已有工作表重名时，后面即使没有重名的工作表也不能添加，所以应先使用错误处理语句忽略错误，待全部添加好以后再删除多余的工作表，如下面的代码所示。

#001  Sub Addsh_5()

#002      Dim i As Integer, arr

#003      Dim sh As Worksheet

#004      On Error Resume Next

#005      arr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

#006      For i = 0 To UBound(arr)

#007          With Worksheets

#008              Set sh = .Add(after:=Sheets(.Count))

#009              sh.Name = arr(i)

#010          End With

#011      Next

#012      Application.DisplayAlerts = False

#013      For Each sh In Worksheets

#014          If sh.Name Like "Sheet*" Then sh.Delete

#015      Next

#016      Application.DisplayAlerts = True

#017  End Sub

代码解析：

Addsh_5过程使用Add方法在工作簿中添加10张工作表并重新命名为1到10，如果工作簿中已有相同名称的工作表则不添加。

第4行代码错误处理语句，当发生重名错误时忽略错误，继续添加工作表。

第5行到第11行代码在工作簿中添加10张工作表并重新命名为1到10，如果工作簿中已有相同名称的工作表则忽略错误重命名时发生的错误，此时工作簿中添加的工作表会以系统赋与的名称命名，如“Sheet1”。

第12行到第15行代码使用For Each...Next语句遍历工作簿中所有的工作表，将工作簿中凡是以“Sheet”开头的工作表删除。

技巧6              禁止删除指定工作表

在工作表事件中是没有工作表删除事件的，为了防止用户误删除重要的工作表，除了使用保护工作簿方法外，还可以使用下面的代码。

#001  Public Ctl As CommandBarControl

#002  Sub DelSht()

#003      Set Ctl = Application.CommandBars.FindControl(ID:=847)

#004      Ctl.OnAction = "MyDelSht"

#005  End Sub

#006  Sub ResSht()

#007      Set Ctl = Application.CommandBars.FindControl(ID:=847)

#008      Ctl.OnAction = ""

#009  End Sub

#010  Sub MyDelSht()

#011      If VBA.UCase$(ActiveSheet.CodeName) = "SHEET2" Then

#012          MsgBox "禁止删除" & ActiveSheet.Name & "工作表!"

#013      Else

#014          ActiveSheet.Delete

#015      End If

#016  End Sub

代码解析：

DelSht过程将工作表标签右键菜单中的“删除工作表”菜单的OnAction属性设置为“MyDelSht”。

第3行代码使用Set语句将工作表标签右键菜单中的“删除工作表”菜单赋给变量Ctl，并将其OnAction属性设置为MyDelSht过程，该菜单被单击时将运行“MyDelSht”过程而不是系统默认的设置。OnAction属性返回或设置一个VBA的过程名，该过程在用户单击或更改某命令栏控件的值时运行。

ResSht过程将工作表标签右键菜单中的“删除工作表”菜单的OnAction属性恢复为默认设置。

MyDelSht过程判断所要删除的工作表的代码名称是否是“SHEET2”，如果是则禁止删除该表而只显示一个提示消息框。

为了不影响其他工作簿的使用，在VBE中双击ThisWorkbook写入下面的代码。

#001  Private Sub Workbook_Activate()

#002      Call DelSht

#003  End Sub

#004  Private Sub Workbook_Deactivate()

#005      Call ResSht

#006  End Sub

代码解析：

工作簿的Activate事件和Deactivate事件代码，在工作簿激活时运行DelSht过程，在关闭或打开其他工作簿时运行ResSht过程，这样只禁止删除本工作簿中“SHEET2”工作表，并不影响其他工作簿。

当删除本工作簿中的“SHEET2”工作表时，并不会显示如图 26?1所示的消息框，而只会显示如图 26?2所示的禁止删除工作表的消息框。

图 26?1   删除工作表提示

图 26?2   禁止删除工作表

技巧7               自动建立工作表目录

如果在工作簿中有许多工作表，使用时往往会建立一张目录表并插入超链接以方便选择工作表。但是如果工作簿中的工作表经常添加和删除，使用手工建立目录很不方便，此时可以使用工作表的Activate事件自动建立工作表的目录，如下面的代码所示。

#001  Private Sub Worksheet_Activate()

#002      Dim sh As Worksheet

#003      Dim a As Integer

#004      Dim R As Integer

#005      R = Sheet1.[A65536].End(xlUp).Row

#006      a = 2

#007      If Sheet1.Cells(2, 1) <> "" Then

#008          Sheet1.Range("A2:A" & R).ClearContents

#009      End If

#010      For Each sh In Worksheets

#011          If sh.CodeName <> "Sheet1" Then

#012              Sheet1.Cells(a, 1).Value = sh.Name

#013              a = a + 1

#014          End If

#015      Next

#016  End Sub

代码解析：

工作表的Activate事件，在“目录”工作表激活时自动建立工作簿中除“目录”工作表外所有工作表的目录。

第2、3、4行代码声明变量类型。

第5行代码取得A列最后非空单元格的行号。

第6行代码设置变量a的初始值为2，从A2单元格开始建立工作表目录。

第7行到第9行代码判断是否存在工作表目录，如果存在先清空原来的目录，以便更新目录。

第10行到第15代码遍历工作簿的所有工作表，将除“目录”工作表外所有工作表的名称写入到A列单元格中。

为了建立到各工作表的链接，使用工作表的SelectionChange事件，如下面的代码所示。

#001  Private Sub Worksheet_SelectionChange(ByVal Target As Range)

#002      Dim R As Integer

#003      R = Sheet1.[A65500].End(xlUp).Row

#004      On Error Resume Next

#005      If Target.Count = 1 Then

#006          If Target.Column = 1 Then

#007              If Target.Row > 1 And Target.Row <= R Then

#008                  Sheets(Target.Value).Select

#009              End If

#010          End If

#011      End If

#012  End Sub

代码解析：

工作表的SelectionChange事件，当选择A列工作表目录中工作表名称时自动选择该单元格所对应的工作表。

第5、6、7行代码限制该事件触发的条件。

第8行代码选择单元格所对应的工作表。

“目录”工作表激活后自动在A列建立工作簿中除“目录”工作表以外所有表的目录，如图 27?1所示。

图 27?1   自动建立工作表目录

技巧8              工作表的深度隐藏

在使用VBA开发的工作簿文件完成交与用户使用后，我们往往希望用户在打开工作簿时启用宏，此时除了使用“禁用宏则关闭工作簿”的功能外，还可以隐藏所有有数据的工作表，如果用户在打开工作簿时禁用宏则只显示一张空白的工作表，达到强制启用宏的效果，代码如下：

#001  Dim sh As Worksheet

#002  Private Sub Workbook_BeforeClose(Cancel As Boolean)

#003      Sheet1.Visible = True

#004      For Each sh In ThisWorkbook.Sheets

#005          If sh.Name <> "空白" Then

#006              sh.Visible = xlSheetVeryHidden

#007          End If

#008      Next

#009      ActiveWorkbook.Save

#010  End Sub

#011  Private Sub Workbook_Open()

#012      For Each sh In ThisWorkbook.Sheets

#013          If sh.Name <> "空白" Then

#014              sh.Visible = xlSheetVisible

#015          End If

#016      Next

#017      Sheet1.Visible = xlSheetVeryHidden

#018  End Sub

代码解析：

第2行到第10行代码是工作簿的BeforeClose事件过程，在工作簿关闭前隐藏除“空白”表以外的所有的工作表。

第3行代码将“空白”表的Visible属性设置为True，使其可见。

应用于Charts和Worksheets对象的Visible属性决定对象是否可见，语法如下：

expression.Visible

参数expression是必需的，该表达式返回上面的对象之一。

Visible属性可以设置为表格 28?1所示的XlSheetVisibility常量之一。

常量	值	描述
xlSheetHidden	0	隐藏对象，可以通过“格式”→“工作表”→“取消隐藏”菜单使对象重新可见，等同于设置为False。
xlSheetVisible	-1	使对象重新可见，等同于设置为True。
xlSheetVeryHidden	2	隐藏对象，使该对象重新可见的唯一方法是将此属性设置为True或xlSheetVisible。

表格 28?1       XlSheetVisibility常量

第4行到第8行代码使用For Each...Next语句遍历工作簿中所有的工作表，将除“空白”表以外的所有工作表的Visible属性设置为xlSheetVeryHidden，使之隐藏。

Visible属性设置为xlSheetVeryHidden后工作表不能通过“格式”→“工作表”→“取消隐藏”菜单来显示隐藏的工作表。

第9行代码使用Save方法保存代码所在工作簿的更改，在关闭工作簿时不显示如图 28?1所示的消息框。

图 28?1   工作簿保存提示

第10行到第18行代码是工作簿的Open事件过程，在打开工作簿时将除“空白”表以外的所有工作表的Visible属性设置为xlSheetVisible，取消隐藏。如果打开工作簿时禁用宏，则工作簿中除了“空白”表以外，其他的工作表还处于深度隐藏的状态，如图 28?2所示，这样就达到强制用户启用宏的效果，当然这还需要VBA工程保护的配合。

图 28?2   工作表深度隐藏

技巧9              防止更改工作表的名称

工作表的名称显示在工作表标签上，除了在相应的功能菜单中可以对其进行重命名操作外，在工作表标签上双击鼠标也能修改工作表名称。一旦修改了工作表名称，可能就会产生一连串的问题，例如在其他工作簿中对该工作表的引用将会失效，通过工作表名称引用工作表的代码也将出错。

Excel没有提供修改工作表名称的相关事件，要禁止用户修改工作表名称，需采取其他一些技巧。比如在工作表BeforeClose事件中检验工作表名称，如果工作表名称不是指定的字符串，则将其修改为指定字符串，即保持工作表名称不变，代码如下。

#001  Private Sub Workbook_BeforeClose(Cancel As Boolean)

#002      If Sheet1.Name <> "Excel Home" Then Sheet1.Name = "Excel Home"

#003      ThisWorkbook.Save

#004  End Sub

代码解析：

工作簿的BeforeClose事件过程，在关闭当前工作簿时判断Sheet1工作表名称，如果不是指定的字符串“Excel Home”，则将其恢复为“Excel Home”后保存工作簿，从而避免更改Sheet1工作表名称。

技巧10           工作表中一次插入多行

在工作表的中插入多行空行，需要使用Insert方法，如下面的代码所示。

#001  Sub InSertRows_1()

#002      Dim i As Integer

#003      For i = 1 To 3

#004          Sheet1.Rows(3).Insert

#005      Next

#006  End Sub

代码解析：

InSertRows_1过程使用Insert方法在如图 30?1所示的数据区域的第2行和第3行之间插入三行空行。

图 30?1   数据区域

Insert方法应用于Range对象时在工作表或宏表中插入一个单元格或单元格区域，其他单元格作相应移位以腾出空间，语法如下：

expression.Insert(Shift, CopyOrigin)

参数expression是必需的，该表达式返回一个Range对象。

参数Shift是可选的，指定单元格的移动方向。可为以下XlInsertShiftDirection常量之一：xlShiftToRight或xlShiftDown。如果省略本参数，Microsoft Excel将依据该区域的形状决定移动方向。

参数CopyOrigin是可选的，复制的起点。

还可以使用引用多行的方法，如下面的代码所示。

#001  Sub InSertRows_2()

#002      Sheet2.Range("A3").EntireRow.Resize(3).Insert

#003  End Sub

代码解析：

InSertRows_2过程通过引用多行区域的方法实现一次插入多行。

第2行代码中的Range(“A3”).EntireRow属性返回Range(“A3”)单元格所在的一整行，然后使用Resize属性调整行数后插入三行空行。

也可以直接指定相应行再调整行数后插入空行，如下面的示例代码：

#001  Sub InSertRows_3()

#002      Sheet3.Rows(3).Resize(3).Insert

#003  End Sub

运行以上过程，工作表中如图 30?2所示。

图 30?2   插入三行空行

技巧11            删除工作表中的空行

如果需要删除如图 31?1所示的工作表中所有的空行，可以使用下面的代码。

图 31?1   需删除空行的工作表区域

#001  Sub DelBlankRow()

#002      Dim rRow As Long

#003      Dim LRow As Long

#004      Dim i As Long

#005      rRow = Sheet1.UsedRange.Row

#006      LRow = rRow + Sheet1.UsedRange.Rows.Count - 1

#007      For i = LRow To rRow Step -1

#008          If Application.WorksheetFunction.CountA(Rows(i)) = 0 Then

#009              Rows(i).Delete

#010          End If

#011      Next

#012  End Sub

代码解析：

DelBlankRow过程删除工作表中已使用的区域的所有空行。

第5行代码获得工作表中已使用区域的首行行号，其中使用UsedRange属性返回工作表中已使用的区域。

第6行代码获得工作表中已使用区域的最后一行行号。

第7行到第11行代码从最大行数至最小行数循环判断指定行是否为空行，若为空行则删除该行。

注意 此处一定要从最大行数至最小行数开始循环判断，因为如果工作表中存在两行及两行以上的相邻空行，从最小行数开始循环删除的话，当第一行空行被删除后，被删除行下面的一行会往上移位，而此时For...Next循环的计数器已经加1，所以会出现漏删除的现象。

其中第8、9行代码使用工作表CountA函数判断当前行已使用单元格的数量，如果为零说明此行是空行则使用Delete删除。

应用于Range对象的Delete方法删除对象，语法如下：

expression.Delete(Shift)

参数expression是必需的，返回一个Range对象。

参数Shift是可选的，指定删除单元格时替补单元格的移位方式。可为以下 XlDeleteShiftDirection常量之一：xlShiftToLeft或xlShiftUp。如果省略该参数，则Microsoft Excel将根据区域的图形决定移位方式。

运行DelBlankRow过程工作表区域如图 31?2所示。

图 31?2   删除空行的工作表区域

技巧12           删除工作表的重复行

在实际应用中，可能需要删除如图 32?1所示的工作表中A列的重复内容而只保留一行，那么可以借助工作表CountIf函数来完成，如下面的代码所示。

图 32?1   需删除重复行的工作表区域

#001  Sub DeleteRow()

#002      Dim R As Integer

#003      Dim i As Integer

#004      With Sheet1

#005          R = .[a65536].End(xlUp).Row

#006          For i = R To 1 Step -1

#007              If WorksheetFunction.CountIf(.Columns(1), .Cells(i, 1)) > 1 Then

#008                  .Rows(i).Delete

#009              End If

#010          Next

#011      End With

#012  End Sub

代码解析：

DeleteRow过程删除工作表A列重复单元格所在的整行内容，只保留一行。

第5行代码取得工作表中A列的最后一个非空单元格的行号，关于Range对象的End属性请参阅技巧3 。

第6行到第10行代码从最大行数至最小行数循环判断A列单元格内容是否重复并删除重复单元格所在的整行。和技巧32 一样，此处For...Next循环也要从最大行数至最小行数开始循环判断，否则可能会删除不净。其中第7、8行代码使用工作表CountIf函数判断单元格内容是否重复，如果重复则删除该单元格所在的行。

运行DeleteRow过程工作表区域如图 32?2所示。

图 32?2   删除重复行的工作表区域

技巧13           定位删除特定内容所在的行

如果需要删除如图 33?1所示的工作表区域中特定内容所在的行，可以使用定位的方法快速删除，无需使用For...Next循环对单元格逐个进行判断。

图 33?1   需删除的工作表区域

示例代码如下：

#001  Sub SpecialDelete()

#002      Dim R As Integer

#003      With Sheet1

#004          R = .Range("a65536").End(xlUp).Row

#005          .Range("a2:a" & R).Replace "Excel", "", 2

#006          .Columns(1).SpecialCells(4).EntireRow.Delete

#007      End With

#008  End Sub

代码解析：

SpecialDelete过程删除工作表A列单元格中显示为“Excel”的行。

第5行代码使用Replace方法将工作表A列中显示为“Excel”的单元格内容替换成空白。关于Replace方法请参阅技巧6 。

第6行代码使用SpecialCells方法定位到工作表A列中所有的空单元格，使用Range对象的EntireRow属性返回其所在的整个行一次性删除。关于SpecialCells方法请参阅技巧4 。

运行SpecialDelete过程工作表区域如图 33?2所示。

图 33?2   删除后的工作表区域

技巧14          判断是否选中整行

通过当前选择的单元格区域的单元格数目与行数或列数相比较，判断用户是否选中了整行或整列，如下面的代码所示。

#001  Private Sub Worksheet_SelectionChange(ByVal Target As Range)

#002      If Target.Rows.Count = 1 Then

#003          If Target.Columns.Count = 256 Then

#004              MsgBox "您选中了整行,当前行号" & Target.Row

#005          End If

#006      End If

#007  End Sub

代码解析：

工作表的SelectionChange事件，判断用户是否选中了工作表中一整行单元格区域。

第2行代码中的Target.Rows.Count返回目标区域的行数，确定用户当前选择区域的总行数是否为1。

第3行代码中的Target.Columns.Count返回目标区域的列数，确定用户当前选择区域总列数是否为256。

第4行代码当用户选中一整行时显示一个消息框，提示用户当前选择的行号，如图 34?1所示。

图 34?1   判断用户是否选中整行

如果需要判断是否选中了整列，只需将上面代码中的总行数修改为65536，总列数修改为1。

技巧15      限制工作表的滚动区域

如果希望限制工作表中滚动的区域，可以通过设置WorkSheet对象的ScrollArea属性来实现。ScrollArea属性使用以A1样式的区域引用形式（字符串类型）返回或设置工作表允许滚动的区域。当设置了工作表滚动区域之后，用户不能选定滚动区域之外的单元格，但仍然可以选定区域之外的其他对象（例如图形、按钮等），同时工作表的一些相应功能可能被禁止（例如工作表全选、选中整行或整列等）。

在VBE中的工程管理窗口选择相应工作表对象，然后在其属性窗口中设置ScrollArea属性，即可限制工作表中滚动的区域，如图 35?1所示。

图 35?1   设置ScrollArea属性

但是Excel不会记忆该项设置，当再次打开该工作簿时，ScrollArea属性将被重置，用户必须重新设置ScrollArea属性才能限制工作表中的滚动区域，解决方法是使用代码在工作簿打开时对ScrollArea属性进行设置，如下面的代码所示。

#001  Private Sub Workbook_Open()

#002      Sheet1.ScrollArea = "B4:H12"

#003  End Sub

代码解析：

工作簿的Open事件，在打开该工作簿时设置Sheet1工作表的滚动区域为“B4:H12”单元格区域。

如果需要取消滚动区域的限制，可以将ScrollArea属性值设置为空，如下面的代码所示。

Sheet1.ScrollArea = ""

技巧16           复制自动筛选后的数据区域

用户在对如图 36?1所示的数据列表进行自动筛选后，往往希望将自动筛选的结果复制到其它地方。

图 36?1   筛选结果

这时可以通过获取该列表区域中可见单元格的方法得到筛选结果的单元格区域，并复制到工作表Sheet2中，如下面的代码所示。

#001  Sub CopyFilter()

#002      Sheet2.Cells.Clear

#003      With Sheet1

#004          If .FilterMode Then

#005              .AutoFilter.Range.SpecialCells(12).Copy Sheet2.Cells(1, 1)

#006          End If

#007      End With

#008  End Sub

代码解析：

CopyFilter过程将Sheet1表中的筛选结果复制到工作表Sheet2中。

第2行代码清除Sheet2表中数据。

第4行代码判断Sheet1表是否处于自动筛选状态。FilterMode属性返回工作表是否处于筛选模式，如果指定工作表中包含已筛选序列且该序列中含有隐藏行，则该值为True。

第5行代码通过AutoFilter对象的Range属性返回工作表的自动筛选列表区域，再使用SpecialCells方法获取该列表区域中可见单元格（SpecialCells方法请参阅技巧4 ），得到筛选结果的单元格区域，然后使用Copy方法将结果区域复制到工作表Sheet2中，应用于Range对象的Copy方法将单元格区域复制到指定的区域或剪贴板中，语法如下：

expression.Copy(Destination)

参数expression是必需的，该表达式返回一个Range对象。

参数Destination是可选的，指定区域要复制到的目标区域。如果省略该参数，则将该区域复制到剪贴板中。

运行CopyFilter过程工作表Sheet2如图 36?2所示。

图 36?2   复制筛选区域

技巧17           使用高级筛选获得不重复记录

在如图 37?1所示的数据列表中，如果要将其中不重复的记录复制到另一工作表中，则可以通过高级筛选功能实现。

图 37?1   数据列表

示例代码如下：

#001  Sub Filter()

#002      Sheet1.Range("A1").CurrentRegion.AdvancedFilter _

#003          Action:=xlFilterCopy, Unique:=True, _

#004          CopyToRange:=Sheet2.Range("A1")

#005  End Sub

代码解析：

Filter过程使用AdvancedFilter方法对单元格A1的当前区域筛选不重复的记录，并将筛选结果复制到工作表Sheet2中。应用于Range集合的AdvancedFilter方法语法如下：

AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)

参数Action是必需的，可以为表格 37?1所列的XlFilterAction常量之一。

XlFilterAction 常量	值	描述
xlFilterCopy	2	将筛选结果复制到其他位置
xlFilterInPlace	1	在原有区域显示筛选结果

表格 37?1       XlFilterAction常量

参数CriteriaRange指定高级筛选操作的条件区域，缺省时表示没有条件限制。

参数CopyToRange表示指定被复制行的目标区域，仅当Action为xlFilterCopy时有效，否则忽略本参数。

参数Unique指示是否选择不重复的记录，如果其值为True，则重复出现的记录仅保留一条；如果其值为 False（默认值），则筛选出所有符合条件的记录。

运行FilterUnique过程，结果如图 37?2所示。

图 37?2   得到不重复记录结果

技巧18           工作表的保护与解除保护

在在实际应用中，Excel编制的报表、表格、程序等，往往在单元格中设置了公式、函数、自定义格式等，为了防止在使用过程中修改或无意中修改这些设置，一般使用Excel的工作表保护功能来保护这些设置。

但是程序中可能会使用代码对受保护的工作表进行操作，此时如果没有解除工作表保护，运行出现错误，如图 38?1所示。

图 38?1   运行错误提示

解决方法是在运行操作工作表的代码前先使用代码解除工作表保护，待操作完毕后再保护工作表，如下面的代码所示。

#001  Sub ShProtect()

#002      With Sheet1

#003          .Unprotect Password:="12345"

#004          .Cells(1, 1) = 100

#005          .Protect Password:="12345"

#006      End With

#007  End Sub

代码解析：

ShProtect过程在受保护的工作表中对单元格进行操作，其中第3行代码使用Unprotect方法解除工作表的保护。应用于Worksheet 对象的Unprotect方法解除工作表的保护，如果工作表不是受保护的，则此方法不起作用，所以在解除之前无需判断工作表是否受保护，其语法如下：

expression.Unprotect(Password)

参数expression是必需的，该表达式返回一个Worksheet 对象。

参数Password是可选的，指定用于解除工作表的保护的密码，此密码是区分大小写的。

第4、5行代码在单元格录入数据后使用Protect方法重新保护工作表。应用于Worksheet对象的Protect方法保护工作表使其不至被修改，语法如下：

expression.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns, AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows, AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows, AllowSorting, AllowFiltering, AllowUsingPivotTables)

其中参数expression是必需的，该表达式返回一个Worksheet对象。

参数Password是可选的，为一个字符串，该字符串为工作表指定区分大小写的密码。

其他参数都是可选参数，其功能等同于如图 38?2所示的工作表保护对话框中的各项选项，具体请参阅VBA帮助。

图 38?2   工作表保护选项

如果一个Excel文件使用时间过长，忘记了工作表保护时设置的密码，那么也可以使用VBA解除工作表的保护，代码如下：

#001  Sub RemoveShProtect()

#002      Dim i1 As Integer, i2 As Integer, i3 As Integer

#003      Dim i4 As Integer, i5 As Integer, i6 As Integer

#004      Dim i7 As Integer, i8 As Integer, i9 As Integer

#005      Dim i10 As Integer, i11 As Integer, i12 As Integer

#006      On Error Resume Next

#007      If ActiveSheet.ProtectContents = False Then

#008          MsgBox "该工作表没有保护密码！"

#009          Exit Sub

#010      End If

#011      For i1 = 65 To 66: For i2 = 65 To 66: For i3 = 65 To 66

#012      For i4 = 65 To 66: For i5 = 65 To 66: For i6 = 65 To 66

#013      For i7 = 65 To 66: For i8 = 65 To 66: For i9 = 65 To 66

#014      For i10 = 65 To 66: For i11 = 65 To 66: For i12 = 32 To 126

#015          ActiveSheet.Unprotect Chr(i1) & Chr(i2) & Chr(i3) & Chr(i4) & Chr(i5) _

#016          & Chr(i6) & Chr(i7) & Chr(i8) & Chr(i9) & Chr(i10) & Chr(i11) & Chr(i12)

#017          If ActiveSheet.ProtectContents = False Then

#018              MsgBox "已经解除了工作表保护！"

#019              Exit Sub

#020          End If

#021      Next: Next: Next: Next: Next: Next

#022      Next: Next: Next: Next: Next: Next

#023  End Sub

代码解析：

RemoveShProtect过程解除工作表的保护。

其中第7行到第10行代码判断工作表是否受保护，ProtectContents属性返回工作表的保护状态，如果工作表的内容处于保护状态，则该值为True。

第11行到第22行代码使用For...Next 语句和Chr函数来返回指定字符码所代表的字符串组合不断地尝试解除工作表保护，一旦工作表的ProtectContents属性返回False说明已经解除工作表保护。

技巧19           奇偶页打印

在Excel中却没有提供打印奇数页和偶数页的功能，用户可以使用VBA在Excel中实现该功能，如下面的代码所示。

#001  Sub PrintOddPage()

#002      Dim TotalPg As Integer

#003      TotalPg = ExecuteExcel4Macro("GET.DOCUMENT(50)")

#004      For i = 1 To TotalPg Step 2

#005          ActiveSheet.PrintOut From:=i, To:=i

#006      Next

#007  End Sub

代码解析：

第3行代码使用Excel 4.0宏表函数获取总页数，通过该函数获取总页数而无需判断分页符。

第4行到第6行代码逐页打印所有奇数页，其中第5行代码中的参数From和To指定打印的开始页数和结束页数。

将第4行代码中的数值改为2，则实现偶数页的打印。