EXCEL个常用宏,非常实用

宏文件集

▲打开全部隐藏工作表返回

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 录制宏时调用停止录制工具栏()

mandBars("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),或命名单元名字()

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 Not Application.Intersect(Target, Range("A4:A9","C4:C9")) Is Nothing Then Call 打开隐藏表End Sub

▲在多个宏中依次循环执行一个（控件按钮代码）返回Private Sub CommandButton1_Click()

Static RunMacro As Integer

Select Case RunMacro

Case 0

宏1

RunMacro = 1

Case 1

宏2

RunMacro = 2

Case 2

宏3

RunMacro = 0

End Select

End Sub