Excel常用宏大全.docxVIP

Excel 常用宏大全（一） 259个常用宏-excelhome-LangQueS(1) 2008-04-01 17:21 打开全部隐藏工作表 Sub 打开全部隐藏工作表() Dim i As Integer For i = 1 To Sheets.Count ??? Sheets(i).Visible = True Next i End Sub 循环宏 Sub 循环() AAA = Range(C2) Dim i As Long Dim times As Long times = AAA ??? times代表循环次数，执行前把times赋值即可(不可小于1，不可大于2147483647) For i = 1 To times Call 过滤一行 ?? If Range(完成标志) = 完成 Then Exit For 如果名为完成标志的命名单元的值等于完成,则退出循环,如果一开始就等于完成,则只执行一次循环就退出 If Sheets(传送参数).Range(A i).Text = 完成 Then Exit For?????? 如果某列出现完成内容则退出循环 Next i End Sub 录制宏时调用“停止录制”工具栏 Sub 录制宏时调用停止录制工具栏() Application.CommandBars(Stop Recording).Visible = True End 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 _ ??????? :=xlPinYin End Sub 双击单元执行宏(工作表代码) Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Range($A$1) = 关闭 Then Exit Sub Select Case Target.Address ?? Case $A$4 ???? Call 宏1 ???? Cancel = True ?? Case $B$4 ???? Call 宏2 ???? Cancel = True ?? Case $C$4 ???? Call 宏3 ???? Cancel = True Case $E$4 ???? Call 宏4 ???? Cancel = True End Select End Sub 双击指定区域单元执行宏(工作表代码) Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Range($A$1) = 关闭 Then Exit Sub If 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 Sub Select Case Target.Address ?? Case $A$5 单元地址(Target.Address),或命名单元名字(Target.Name) ???? Call 宏1 ?? Case $B$5 ???? Call 宏2 ?? Case $C$5 ???? Call 宏3 End Select End Sub 进入指定区域单元执行宏(工作表代码) Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Range($A$1) = 关闭 Then Exit Sub If No