EXCEL宏编辑命令(6)

64、按固定文本定位

Sub 文本定位() Dim aa As Range

For Each a In ActiveSheet.UsedRange If a Like \合计*\ If aa Is Nothing Then Set aa = a.Cells

Else

Set aa = Union(aa, a.Cells) End If End If Next aa.Select

End Sub

65、删除包含固定文本单元的行或列

Sub 删除包含固定文本单元的行或列()

Do

Cells.Find(what:=\哈哈\

Selection.EntireRow.Delete '删除行 ' Selection.EntireColumn.Delete '删除列

Loop Until Cells.Find(what:=\哈哈\End Sub

66、定位数据及区域以上的空值

Sub 定位数据及区域以上的空值() Dim aa As Range

For Each a In ActiveSheet.UsedRange If a Like 〈0 Then If aa Is Nothing Then Set aa = a.Cells Else

Set aa = Union(aa, a.Cells) End If End If Next aa.Select

End Sub

67、右侧单元自动加5(工作表代码)

Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False Target.Offset(0, 1) = Target + 5 Application.EnableEvents = True End Sub

68、当前单元加2

Sub 当前单元加2()

Selection = Selection + 2

'Selection = Workbooks(\临时表\表2\调用指定地址内容 End Sub

69、A列等于A列减B列

Sub A列等于A列减B列() For i = 1 To 23

Cells(i, 1) = Cells(i, 1) - Cells(i, 2) Next End Sub

70、用于光标选定多区域跳转指定单元(工作表代码)

Private Sub Worksheet_SelectionChange(ByVal T As Range) a = Array([b6:b7], [e6], [h6]) For i = 0 To 2

If Not Application.Intersect(T, a(i)) Is Nothing Then [a1].Select: Exit For End If Next End Sub

71、将A1单元录入的数据累加到B1单元（工作表代码）

Private Sub Worksheet_Change(ByVal Target As Range)

Dim t As Long

If Target.Address = \t = Sheet1.Range(\alue Sheet1.Range(\alue = t + Target.Value End If End Sub

72、在指定颜色区域选择单元时添加/取消\√\（工作表代码）

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim myrg As Range

For Each myrg In Target

If myrg.Interior.ColorIndex = 37 Then myrg = IIf(myrg <> \√\√\ Next End Sub

73、在指定区域选择单元时添加/取消\√\（工作表代码）

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Rng As Range

If Target.Count <= 15 Then

If Not Application.Intersect(Target, Range(\ For Each Rng In Selection With Rng If .Value = \ .Value = \√\ Else .Value = \ End If End With Next End If End If End Sub

74、双击指定单元，循环录入文本（工作表代码）

Private Sub Worksheet_BeforeDoubleClick(ByVal T As Range, Cancel As Boolean) If T.Address <> \Cancel = True

T = IIf(T = \好\中\中\差\好\

End Sub

75、双击指定单元，循环录入文本（工作表代码）

Dim nums As Byte

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Target.Address = \nums = nums Mod 3 + 1

Target = Mid(\上中下\Target.Offset(1, 0).Select End If End Sub

76、单元区域引用（工作表代码）

Private Sub Worksheet_Activate() Sheet1.Range(\alue = Sheet2.Range(\alue End Sub

77、在指定区域选择单元时数值加1（工作表代码）

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Application.Intersect([a1:e10], Target) Is Nothing Then Target = Val(Target) + 1 End If End Sub

259个常用宏-excelhome(3) 2009-08-15 14:12:58

78、混合文本的编号

Sub 混合文本的编号()

Worksheets(1).Range(\alue = \北京\End Sub

79、指定区域单元双击数据累加（工作表代码）

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Application.Intersect([A1:Y100], Target) Is Nothing Then oldvalue = Val(Target.Value)

inputvalue = InputBox(\请输入数量,按ENTER键确认!\数值累加器\Target.Value = oldvalue + inputvalue End If End Sub

80、选择单元区域触发事件（工作表代码）

Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Address = \ MsgBox \你选择了$A$1:$B$2单元\End If

End Sub

81、当修改指定单元内容时自动执行宏（工作表代码）

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, [B3:B4]) Is Nothing Then 重排窗口 End If End Sub

82、被指定单元内容限制执行宏

Sub 被指定单元限制执行宏()

If Range(\关闭\窗口 End Sub

83、双击单元隐藏该行（工作表代码）

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Rows(Target.Row).Hidden = True End Sub

84、高亮显示行（工作表代码）

百度搜索“77cn”或“免费范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，免费范文网，提供经典小说综合文库EXCEL宏编辑命令(6)在线全文阅读。