Excle-VBA常用技巧-第02章-Worksheet对象

VBA常用技巧代码解析1VBA常用技巧目录VBA常用技巧...................................................................................................................................1第2章Worksheet（工作表）对象..................................................................................2技巧1引用工作表的方式...........................................................................................21-1使用工作表的名称.........................................................................................21-2使用工作表的索引号.....................................................................................21-3使用工作表的代码名称.................................................................................31-4使用ActiveSheet属性引用活动工作表.......................................................3技巧2选择工作表的方法...........................................................................................4技巧3遍历工作表的方法...........................................................................................53-1使用For...Next语句.....................................................................................53-2使用ForEach...Next语句............................................................................7技巧4在工作表中上下翻页.......................................................................................8技巧5工作表的添加与删除.......................................................................................9技巧6禁止删除指定工作表.....................................................................................13技巧7自动建立工作表目录.....................................................................................15技巧8工作表的深度隐藏.........................................................................................17技巧9防止更改工作表的名称.................................................................................19技巧10工作表中一次插入多行...............................................................................20技巧11删除工作表中的空行...................................................................................21技巧12删除工作表的重复行...................................................................................23技巧13定位删除特定内容所在的行.......................................................................25技巧14判断是否选中整行.......................................................................................26技巧15限制工作表的滚动区域...............................................................................27技巧16复制自动筛选后的数据区域.......................................................................28技巧17使用高级筛选获得不重复记录...................................................................30技巧18工作表的保护与解除保护...........................................................................31技巧19奇偶页打印...................................................................................................34VBA常用技巧代码解析2第2章Worksheet（工作表）对象技巧1引用工作表的方式VBA中，在不同的工作表之间转换或者对不同工作表中的单元格区域进行操作时，需要指定引用的工作表，通常有下面几种方法：1-1使用工作表的名称工作表名称是指显示在工作表标签中的文本，工作表名称可以使用WorkSheets集合和Sheets集合两种引用方式，如下面的代码所示。#001SubShActivate()#002Worksheets(索引号).Activate#003'Sheets(索引号).Activate#004EndSub第3、4行代码都激活工作簿中名称为“索引号”的工作表，激活后“索引号”工作表将成为活动工作表。WorkSheets集合包含所有的工作表，而Sheets集合不仅包含工作表集合WorkSheets，还包含图表集合Charts、宏表集合Excel4MacroSheets与MSExcel5.0对话框集合DialogSheets等。任何时刻工作簿中只有一个工作表是活动工作表。1-2使用工作表的索引号工作表索引号是指工作表在工作簿中的位置，Excel根据工作表在工作表标签中的位置以1开始从左向右进行编号。下面的代码选中并激活当前工作簿中第1个工作表：#001SubShIndex()#002Worksheets(1).Select#003EndSubVBA常用技巧代码解析3单个WorkSheet对象的Select方法与Activate方法的主要区别在于Select方法要求工作表可视。注意当工作簿包括工作表、宏表、图表等时，使用索引号引用工作表如Sheets(1)与WorkSheets(1)引用的可能不是同一个表。使用Worksheet对象的Index属性可以返回工作表的索引号，如下面的代码所示。#001SubShInde()#002MsgBoxWorksheets(索引号).Index#003EndSub1-3使用工作表的代码名称使用Worksheet对象的CodeName属性可以返回工作表的代码名称，如下面的代码所示。#001SubShCodeName()#002MsgBoxSheets(1).CodeName#003EndSub工作表的代码名称显示在VBE工程资源管理器窗口中，在属性窗口中能够修改工作表代码名称，如图1-1所示。在VBA中能够直接使用工作表的代码名称引用工作表，即使工作表的名称被修改，代码仍然能够正常运行。图1-1工作表的代码名称1-4使用ActiveSheet属性引用活动工作表使用ActiveSheet属性可以返回活动工作表，如下面的代码所示。VBA常用技巧代码解析4#001SubShActive()#002MsgBoxActiveSheet.Name#003EndSubActiveSheet属性应用于AppActivate对象、Window对象和Workbook对象时，如果未给出对象识别符，返回活动工作簿中的活动工作表。技巧2选择工作表的方法在VBA中需要激活或者选择某个工作表时使用Select方法或Activate方法，如下面的代码所示。#001SubSelectSh()#002Worksheets(Sheet2).Select#003EndSub#004SubActivateSh()#005Worksheets(Sheet2).Activate#006EndSub代码解析：SelectSh过程使用Select方法选择“Sheet2”工作表，而ActivateSh过程则使用Activate方法选择“Sheet2”工作表，从表面看两者的作用是相同的，但是如果“Sheet2”工作表是隐藏的，Activate方法可以正常运行，而Select方法将会出现错误，如图2-1所示。图2-1Select方法无效提示如果需要同时选中工作簿中的所有工作表，则只能使用Select方法而不能使用Activate方法，如下面的代码所示。VBA常用技巧代码解析5#001SubSelectShs()#002DimShsAsWorksheet#003ForEachShsInWorksheets#004Shs.SelectFalse#005Next#006EndSub#007SubSelectSheets()#008Worksheets.Select#009EndSub#010SubArraySheets()#011Worksheets(Array(1,2,3)).Select#012EndSub代码解析：SelectShs过程遍历工作表并使用带参数的Select方法选中所有工作表。应用于Worksheet对象的Select方法的语法如下：Select(Replace)参数Replace是可选的。如果该值为True，则用指定对象替代当前选定对象。如果该值为False，则延伸当前选定对象以包括任何以前选定的对象。SelectSheets过程使用Worksheets集合的Select方法选中集合中所有的对象。ArraySheets过程使用Array函数返回工作簿中的前三张工作表并使用Worksheets集合的Select方法选中前三张工作表。技巧3遍历工作表的方法在Excel应用中经常需要遍历工作簿中所有的工作表，有以下两种方法可以实现。3-1使用For...Next语句使用For...Next语句遍历工作簿中所有的工作表，如下面的代码所示。VBA常用技巧代码解析6#001SubShCount1()#002DimcAsInteger#003