网优257个Excel宏常用语句

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