【正文】 $2 Then MsgBox 你選擇了$A$1:$B$2單元End IfEnd Sub 8當(dāng)修改指定單元內(nèi)容時自動執(zhí)行宏（工作表代碼）Private Sub Worksheet_Change(ByVal Target As Range)If Not (Target, [B3:B4]) Is Nothing Then重排窗口End IfEnd Sub 8被指定單元內(nèi)容限制執(zhí)行宏Sub 被指定單元限制執(zhí)行宏()If Range($A$1) = 關(guān)閉 Then Exit Sub窗口End Sub 8雙擊單元隱藏該行（工作表代碼）Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)Rows().Hidden = TrueEnd Sub 8高亮顯示行（工作表代碼）Private Sub Worksheet_SelectionChange(ByVal Target As Range) = 2Rows(1:2). = 40 39。保持1至2行的顏色推薦39,22,40,Rows(). = 35 39。高亮推薦顏色35,20,24,34,37,40,15End Sub 8高亮顯示行和列（工作表代碼）Private Sub Worksheet_SelectionChange(ByVal Target As Range) = xlNoneRows(). = 34Columns(). = 34End Sub 8為指定工作表設(shè)置滾動范圍（工作簿代碼）Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) = A1:M30End Sub 8在指定單元記錄打印和預(yù)覽次數(shù)（工作簿代碼）Private Sub Workbook_BeforePrint(Cancel As Boolean)Range(A1) = 1 + Range(A1)End Sub 8自動數(shù)字金額轉(zhuǎn)大寫（工作表代碼）Private Sub Worksheet_Change(ByVal M As Range)On Error Resume Nexty = Int(Round(100 * Abs(M)) / 100) j = Round(100 * Abs(M) + ) y * 100 f = (j / 10 Int(j / 10)) * 10 A = IIf(y 1, , (y, [DBNum2]) amp。 元) b = IIf(j , (Int(j / 10), [DBNum2]) amp。 角, IIf(y 1, , IIf(f 1, 零, ))) c = IIf(f 1, 整, (Round(f, 0), [DBNum2]) amp。 分) M = IIf(Abs(M) , , IIf(M 0, 負(fù) amp。 A amp。 b amp。 c, A amp。 b amp。 c))End Sub 8將全部工作表的A1單元作為單擊按鈕（工作簿代碼）Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)If = $A$1 Then Call 宏名End IfEnd Sub 90、鬧鐘——到指定時間執(zhí)行宏（工作簿代碼）Private Sub Workbook_Open() (11:45:00), 提示1 39。宏名字 (12:00:00), 提示2 39。宏名字End Sub 9改變Excel界面標(biāo)題的宏（工作簿代碼）Private Sub Workbook_Open() = 春節(jié)快樂End Sub 9在指定工作表的指定單元返回光標(biāo)當(dāng)前多選區(qū)地址（工作簿代碼）Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)Worksheets(表2).Range(A1) = (0, 0)End Sub 9B列錄入數(shù)據(jù)時在A列返回記錄時間（工作表代碼）Public Sub Worksheet_Change(ByVal Target As Range)If = 2 Then(, 1) = NowEnd IfEnd Sub 9當(dāng)指定區(qū)域修改時在其右側(cè)的2個單元返回當(dāng)前日期和時間（工作表代碼）Public Sub Worksheet_Change(ByVal Target As Range)If Not (Target, [A1:A1000]) Is Nothing ThenIf = 1 Then(, 1) = Date(, 2) = TimeEnd IfEnd IfEnd SubPublic Sub Worksheet_Change(ByVal Target As Range)If Not (Target, [A1:A1000]) Is Nothing ThenIf = 1 Then(, 1) = Format(Now(), yyyymmdd)(, 2) = Format(Now(), h:mm:ss)End IfEnd IfEnd Sub 9指定單元顯示光標(biāo)位置內(nèi)容（工作表代碼）Private Sub Worksheet_SelectionChange(ByVal T As Range)Sheets(1).Range(A1) = SelectionEnd Sub 9每編輯一個單元保存文件Private Sub Worksheet_Change(ByVal Target As Range)End Sub 9指定允許編輯區(qū)域Sub 指定允許編輯區(qū)域() = B8:G15End Sub 9解除允許編輯區(qū)域限制Sub 解除允許編輯區(qū)域限制() = End Sub 9刪除指定行Sub 刪除指定行()Workbooks(臨時表).Sheets(表2).Range(5:5).DeleteEnd Sub 100、刪除A列為指定內(nèi)容的行Sub 刪除A列為指定內(nèi)容的行()Dim a, b As Integera = Sheet1.[a65536].End(xlUp).Row For b = a To 2 Step 1 If Cells(b, 1).Value = 刪除 Then Rows(b).Delete End If NextEnd Sub 10刪除A列非數(shù)字單元行Sub 刪除A列非數(shù)字單元行()i = [a65536].End(xlUp).RowRange(A1:A amp。 i).SpecialCells(xlCellTypeConstants, 2).End Sub 10有條件刪除當(dāng)前行Sub 有條件刪除當(dāng)前行()If [A1] = 2 Or [B1] = 刪除 Then Shift:=xlUpEnd IfEnd Sub 10選擇下一行Sub 選擇下一行() (1, 0).Rows(1:1).End Sub 10選擇第5行開始所有數(shù)據(jù)行Sub 選擇第5行開始所有數(shù)據(jù)行A() Dim i% i = (*, SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious). Rows(5: amp。 i).SelectEnd SubSub 選擇第5行開始所有數(shù)據(jù)行B()Rows(5: amp。 (*, , , , 1, 2).Row).SelectEnd Sub 10選擇光標(biāo)或選區(qū)所在行Sub 選擇光標(biāo)或選區(qū)所在行() End Sub 10選擇光標(biāo)或選區(qū)所在列Sub 選擇光標(biāo)或選區(qū)所在列() End Sub 10光標(biāo)定位到名稱指定位置Sub 定位() Range(Evaluate(名稱))End Sub 10選擇名稱定義的數(shù)據(jù)區(qū)Sub 選擇名稱定義的數(shù)據(jù)區(qū)() [數(shù)據(jù)區(qū)].Select 39。插入名稱要使用INDIRECT函數(shù) 39。Range(數(shù)據(jù)區(qū)).Select 或者 39。(數(shù)據(jù)區(qū)).Select 或者End Sub 10選擇到指定列的最后行Sub 選擇到指定列的最后行()Range(C4:G amp。 [G65536].End(xlUp).Row).SelectEnd Sub 1將Sheet1的A列的非空值寫到Sheet2的A列Sub 將Sheet1的A列的非空值寫到Sheet2的A列() (A:A).SpecialCells(2, 23).SpecialCells(12).Copy Sheet2.[A1]End Sub 11將名稱1的數(shù)據(jù)寫到名稱2Sub Macro2()Range(位置2) = Range(位置1).ValueEnd Sub 11單元反選Sub 單元反選() = False = FalseDim raddress As String, taddress As Stringraddress = taddress = With .Range(taddress) = 0.Range(raddress) = =0raddress = .Range(taddress).SpecialCells(xlCellTypeConstants, 1).Address.DeleteEnd With(raddress).Select = TrueEnd Sub 11調(diào)整選中對象中的文字Sub 調(diào)整選中對象中的文字()39。文字居中、自動調(diào)整大小 With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .ReadingOrder = xlContext .Orientation = xlHorizontal .AutoSize = True .AddIndent = False End WithEnd Sub 11去除指定范圍內(nèi)的對象Sub 去除指定范圍內(nèi)的對象() Dim p As Shape Set My = Worksheets(工作表名) For Each p In If Not (, Range(范圍)) Is Nothing Then NextEnd Sub 11更新透視表數(shù)據(jù)項Sub DeleteMissingItems2002All()39。防止數(shù)據(jù)透視表中顯示無用的數(shù)據(jù)項39。在 Excel 2002 或更高版本中39。如果無用的數(shù)據(jù)項已經(jīng)存在,39。運(yùn)行這個宏可以更新Dim pt As PivotTableDim ws As WorksheetFor Each ws In For Each pt In = xlMissingItemsNoneNext ptNext wsEnd Sub 11將全部工作表名稱寫到A列Sub 將全部表名稱寫到A列()k = 1For Each Sht In SheetsCells(k + 1, 1) = 39。指定寫入的行和列k = k + 1NextEnd Sub 11為當(dāng)前選定的多單元插入指定名稱Sub 為當(dāng)前選定的多單元插入指定名稱() = 臨時 Name:=臨時, RefersTo:=Selection 39?；蛘邠Q用這行代碼也可以End Sub 11刪除全部名稱Sub 刪除全部名稱()On Error Resume NextDim l As Integerl = For i = l To 1 Step 1(i).DeleteNextEnd Sub 11以指定區(qū)域為表目錄補(bǔ)充新表Sub 以指定區(qū)域為表目錄補(bǔ)充新表()Dim dic As Object, sh As WorksheetDim arr, item arr = Range(B1:BB1) Set dic = CreateObject() For Each sh In , Next