Excel常用宏大全.docx

Excel 常用宏大全（一） 259个常用宏-excelhome-LangQueS(1)2008-04-01 17:21打开全部隐藏工作表Sub 打开全部隐藏工作表()Dim i As IntegerFor i = 1 To Sheets.Count??? Sheets(i).Visible = TrueNext iEnd Sub循环宏Sub 循环()AAA = Range(C2)Dim i As LongDim times As Longtimes = AAA??? times代表循环次数，执行前把times赋值即可(不可小于1，不可大于2147483647)For i = 1 To timesCall 过滤一行?? If Range(完成标志) = 完成 Then Exit For 如果名为完成标志的命名单元的值等于完成,则退出循环,如果一开始就等于完成,则只执行一次循环就退出If Sheets(传送参数).Range(A i).Text = 完成 Then Exit For?????? 如果某列出现完成内容则退出循环Next iEnd Sub录制宏时调用“停止录制”工具栏Sub 录制宏时调用停止录制工具栏()Application.CommandBars(Stop Recording).Visible = TrueEnd Sub高级筛选5列不重复数据至指定表Sub 高级筛选5列不重复数据至Sheet2()Sheets(Sheet2).Range(A1:E65536) = 清除Sheet2的A:D列Range(A1:E65536).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Sheet2.Range( _??????? A1), Unique:=True??? Sheet2.Columns(A:E).Sort Key1:=Sheet2.Range(A2), Order1:=xlAscending, Header:=xlGuess, _??????? OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _??????? :=xlPinYinEnd Sub双击单元执行宏(工作表代码)Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)If Range($A$1) = 关闭 Then Exit SubSelect Case Target.Address?? Case $A$4???? Call 宏1???? Cancel = True?? Case $B$4???? Call 宏2???? Cancel = True?? Case $C$4???? Call 宏3???? Cancel = TrueCase $E$4???? Call 宏4???? Cancel = TrueEnd SelectEnd Sub双击指定区域单元执行宏(工作表代码)Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)If Range($A$1) = 关闭 Then Exit SubIf Not Application.Intersect(Target, Range(A4:A9, C4:C9)) Is Nothing Then Call 打开隐藏表End Sub进入单元执行宏(工作表代码)Private Sub Worksheet_SelectionChange(ByVal Target As Range)以单元格进入代替按钮对象调用宏If Range($A$1) = 关闭 Then Exit SubSelect Case Target.Address?? Case $A$5 单元地址(Target.Address),或命名单元名字(Target.Name)???? Call 宏1?? Case $B$5???? Call 宏2?? Case $C$5???? Call 宏3End SelectEnd Sub进入指定区域单元执行宏(工作表代码)Private Sub Worksheet_SelectionChange(ByVal Target As Range)If Range($A$1) = 关闭 Then Exit SubIf Not Application.Intersect(Target, Range(A4:A9,C4:C9)) Is Nothing Then Call 打开隐