VB精华A.docVIP

VB精华A 259个常用宏代码 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