103、选择下一行
Sub 选择下一行()
ActiveCell.Offset(1, 0).Rows(\End Sub
104、选择第5行开始所有数据行
Sub 选择第5行开始所有数据行A() Dim i% i = Cells.Find(\SearchOrder:=xlByRows, SearchDirection:=xlPrevious).EntireRow.Row Rows(\End Sub
Sub 选择第5行开始所有数据行B()
Rows(\End Sub
105、选择光标或选区所在行
Sub 选择光标或选区所在行() Selection.EntireRow.Select End Sub
106、选择光标或选区所在列
Sub 选择光标或选区所在列() Selection.EntireColumn.Select End Sub
107、光标定位到名称指定位置
Sub 定位()
Application.Goto Range(Evaluate(\名称\End Sub
108、选择名称定义的数据区
Sub 选择名称定义的数据区()
[数据区].Select '插入名称要使用INDIRECT函数 'Range(\数据区\ 或者
LookIn:=xlValues,
'Sheet1.Range(\数据区\或者 End Sub
109、选择到指定列的最后行
Sub 选择到指定列的最后行()
Range(\End Sub
110、将Sheet1的A列的非空值写到Sheet2的A列
Sub 将Sheet1的A列的非空值写到Sheet2的A列()
Sheet1.Columns(\End Sub
111、将名称1的数据写到名称2
Sub Macro2()
Range(\位置2\位置1\End Sub
112、单元反选
Sub 单元反选()
Application.DisplayAlerts = False Application.ScreenUpdating = False
Dim raddress As String, taddress As String raddress = Selection.Address
taddress = ActiveSheet.UsedRange.Address With Sheets.Add .Range(taddress) = 0 .Range(raddress) = \
raddress = .Range(taddress).SpecialCells(xlCellTypeConstants, 1).Address .Delete End With
ActiveSheet.Range(raddress).Select Application.ScreenUpdating = True End Sub
113、调整选中对象中的文字
Sub 调整选中对象中的文字()
'文字居中、自动调整大小 With Selection
.HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True .AddIndent = False End With End Sub
114、去除指定范围内的对象
Sub 去除指定范围内的对象() Dim p As Shape
Set My = Worksheets(\工作表名\ For Each p In My.Shapes
If Not Application.Intersect(p.TopLeftCell, Range(\范围\ Next End Sub
115、更新透视表数据项
Sub DeleteMissingItems2002All()
'防止数据透视表中显示无用的数据项 '在 Excel 2002 或更高版本中 '如果无用的数据项已经存在, '运行这个宏可以更新 Dim pt As PivotTable Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone Next pt Next ws End Sub
116、将全部工作表名称写到A列
Sub 将全部表名称写到A列() k = 1
For Each Sht In Sheets
Cells(k + 1, 1) = Sht.Name '指定写入的行和列
k = k + 1 Next End Sub
117、为当前选定的多单元插入指定名称
Sub 为当前选定的多单元插入指定名称() Selection.Name = \临时\
ActiveWorkbook.Names.Add Name:=\临时\ '或者换用这行代码也可以 End Sub
118、删除全部名称
Sub 删除全部名称() On Error Resume Next Dim l As Integer
l = ActiveWorkbook.Names.Count For i = l To 1 Step -1
ActiveWorkbook.Names(i).Delete Next End Sub
119、以指定区域为表目录补充新表
Sub 以指定区域为表目录补充新表() Dim dic As Object, sh As Worksheet Dim arr, item
arr = Range(\
Set dic = CreateObject(\ For Each sh In ThisWorkbook.Worksheets dic.Add sh.Name, \ Next
For Each item In arr
If item <> \ With ThisWorkbook.Worksheets.Add .Name = item End With End If Next
Set dic = Nothing End Sub