中文字幕理论片,69视频免费在线观看,亚洲成人app,国产1级毛片,刘涛最大尺度戏视频,欧美亚洲美女视频,2021韩国美女仙女屋vip视频

原創(chuàng)2019-03-29 11:58·浮云Excel

1.顯示多個(gè)隱藏的工作表

如果你的工作簿里面有多個(gè)隱藏的工作表，你需要花很多時(shí)間一個(gè)一個(gè)的顯示隱藏的工作表。

下面的代碼，可以讓你一次顯示所有的工作表Sub UnhideAllWoksheets()  Dim ws As Worksheet   For Each ws In ActiveWorkbook.Worksheets  ws.Visible = xlSheetVisible   Next ws  End Sub

2.隱藏除了活動(dòng)工作表外的所有工作表

如果你做的報(bào)表，希望隱藏除了報(bào)表工作表以外的所有工作表，則可以用一下代碼來(lái)實(shí)現(xiàn)：Sub HideAllExcetActiveSheet() Dim ws As Worksheet   For Each ws In ThisWorkbook.Worksheets   If ws.Name <> ActiveSheet.Name Then  ws.Visible = xlSheetHidden   End if  Next ws  End Sub

3.用VBA代碼按字母的順序?qū)ぷ鞅磉M(jìn)行排序

如果你有一個(gè)包含多個(gè)工作表的工作簿，并且希望按字母對(duì)工作表進(jìn)行排序，那么下面的代碼，可以派上用場(chǎng)。Sub SortSheetsTabName()  Application.ScreenUpdating = False  Dim ShCount As Integer, i As Integer, j As Integer  ShCount = Sheets.Count   For i = 1 To ShCount - 1   For j = i + 1 To ShCount   If Sheets(j).Name < Sheets(i).Name Then   Sheets(j).Move before:=Sheets(i)   End If   Next j   Next i  Application.ScreenUpdating = True  End Sub

4.一次性保護(hù)所有的工作表

如果工作薄里面有多個(gè)工作表，并且希望保護(hù)所有的工作表，那么下面的代碼，可以派上用場(chǎng)。Sub ProtectAllSheets()  Dim ws As Worksheet  Dim password As String  '用你想要的密碼替換Test123  password = "Test123"  For Each ws In Worksheets   ws.Protect password:=password  Next ws  End Sub

5.一次性取消所有的工作表保護(hù)

如果你保護(hù)了你所有的工作表，那么你只需要修改一下代碼，就可以取消所有工作表的保護(hù)。Sub ProtectAllSheets()  Dim ws As Worksheet  Dim password As String  '用你想要的密碼替換Test123  password = "Test123"   For Each ws In Worksheets   ws.Unprotect password:=password   Next ws  End Sub

需要注意的是，取消保護(hù)工作表的密碼， 要與鎖定工作表的密碼相同，否則程序會(huì)拋出異常（出錯(cuò)）。

6.顯示所有隱藏的行和列

下面的代碼，可以取消所有隱藏的行和列。

如果你從別人那里獲得一個(gè)Excel文件，并希望沒(méi)有隱藏的行與列，那么下面的代碼對(duì)你非常有用。Sub UnhideRowsColumns()   Columns.EntireColumn.Hidden = False   Rows.EntireRow.Hidden = False  End Sub

7.取消所有的合并單元格

把多個(gè)單元格合并成一個(gè)單元格時(shí)常用的做法：

如果你的工作表里面有合并的單元格，使用下面代碼可以一次性取消所有合并的單元格。Sub UnmergeAllCells()  ActiveSheet.Cells.UnMerge  End Sub

8.保存帶有時(shí)間戳的工作簿

很多時(shí)候，您可能需要?jiǎng)?chuàng)建工作的各個(gè)版本。

一個(gè)好的做法，就是在工作薄名稱(chēng)上，加上時(shí)間戳。

使用時(shí)間戳將允許您返回到某個(gè)文件，查看進(jìn)行了哪些更改或使用了哪些數(shù)據(jù)。

下面的代碼會(huì)自動(dòng)保存工作簿在指定的文件夾中,并添加一個(gè)時(shí)間戳?xí)r保存。Sub SaveWorkbookWithTimeStamp()  Dim timestamp As String   timestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & timestamp  End Sub

C:UsersUsernameDesktopWorkbookName 你可以制定文件位置和文件名。

"dd-mm-yyyy"指的的日期的格式。

"hh-ss"指的是時(shí)間的格式

9.將工作表另存為一個(gè)PDF文件

如果您使用不同年份或部門(mén)或產(chǎn)品的數(shù)據(jù)，可能需要將不同的工作表保存為PDF文件。

如果手動(dòng)完成，這可能是一個(gè)耗時(shí)的過(guò)程，但vba確可以加快速度。

下面是一個(gè)將每個(gè)工作表保存為單獨(dú)PDF的VBA代碼Sub SaveWorkshetAsPDF() Dim ws As Worksheet   For Each ws In Worksheets   ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf"   Next ws  End Sub

在上面的代碼中，我指定了要保存pdf的文件夾位置的地址。

請(qǐng)注意，此代碼僅適用于工作表。

10.將工作簿另存為單獨(dú)的PDF文件

下面是將整個(gè)工作簿保存為指定文件夾中的PDF格式的代碼Sub SaveWorkshetAsPDF()  ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf"  End Sub

你可以修改儲(chǔ)存文件的文件件。

注意：9~10代碼保存為PDF文件，需要在工作表里面設(shè)置好打印的區(qū)域。如果有空的工作表，那么程序會(huì)報(bào)錯(cuò)。

11.將所有公式轉(zhuǎn)換為值

如果工作表包含大量公式，并且要將這些公式轉(zhuǎn)換為值，請(qǐng)使用此代碼。Sub ConvertToValues()   With ActiveSheet.UsedRange   .Value = .Value   End With  End Sub

此代碼可以自動(dòng)將使用公式的值轉(zhuǎn)換為值

12.有公式的單元格鎖定

當(dāng)您有大量的計(jì)算并且不想意外的刪除或更改時(shí)，您可能希望使用把有公式的單元格進(jìn)行鎖定。

下面是將鎖定所有具有公式的單元格的代碼，而所有其它單元格都未鎖定。Sub LockCellsWithFormulas()   With ActiveSheet   .Unprotect   .Cells.Locked = False   .Cells.SpecialCells(xlCellTypeFormulas).Locked = True   .Protect AllowDeletingRows:=True   End With  End Sub

13.保護(hù)工作簿中所有的工作表

使用以下代碼一次性保護(hù)工作簿中的所有工作表Sub ProtectAllSheets()  Dim ws As Worksheet   For Each ws In Worksheets   ws.Protect   Next ws  End Sub

此代碼將逐個(gè)瀏覽所有工作表并對(duì)其進(jìn)行保護(hù)。

如果要取消所有工作表的保護(hù)，可以使用 ws.unProtect

14.在所選內(nèi)容中每隔一行后插入一行

如果要在選定區(qū)域中的每一行后插入空行，請(qǐng)使用此代碼。Sub InsertAlternateRows()  Dim rng As Range  Dim CountRow As Integer  Dim i As Integer  Set rng = Selection  CountRow = rng.EntireRow.Count   For i = 1 To CountRow   ActiveCell.EntireRow.Insert   ActiveCell.Offset(2, 0).Select   Next i  End Sub

同樣，您可以修改此代碼，以便在所選范圍內(nèi)的每一列之后插入一個(gè)空白列

15.自動(dòng)在相鄰單元格中插入日期和時(shí)間戳

當(dāng)您想要跟蹤活動(dòng)時(shí)，可以使用時(shí)間戳。

使用此代碼在創(chuàng)建條目或編輯現(xiàn)有內(nèi)容時(shí)在相鄰單元格中插入日期和時(shí)間戳。Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Handler   If Target.Column = 1 And Target.Value <> "" Then   Application.EnableEvents = False   Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss") Application.EnableEvents = True   End If  Handler:  End Sub

請(qǐng)注意，您需要將此代碼插入工作表代碼窗口（而不是模塊內(nèi)代碼窗口）。因?yàn)檫@是一個(gè)事件代碼

16.突出顯示所選內(nèi)容中的可選行

突出顯示可選行可以極大地提高數(shù)據(jù)的可讀性。

下面是一個(gè)代碼，它將立即突出顯示所選內(nèi)容中的可選行。Sub HighlightAlternateRows() Dim Myrange As Range  Dim Myrow As Range  Set Myrange = Selection  For Each Myrow In Myrange.Rows   If Myrow.Row Mod 2 = 1 Then  Myrow.Interior.Color = vbCyan   End If  Next Myrow  End Sub

注意，代碼中指定了顏色為vbCyan（也可以修改成：vbRed, vbGreen, vbBlue）。

17.突出顯示拼錯(cuò)單詞的單元格

Excel沒(méi)有像在Word或PowerPoint中那樣進(jìn)行拼寫(xiě)檢查。雖然可以按F7鍵進(jìn)行拼寫(xiě)檢查，但當(dāng)出現(xiàn)拼寫(xiě)錯(cuò)誤時(shí)，沒(méi)有視覺(jué)提示。

使用此代碼可以立即突出顯示其中有拼寫(xiě)錯(cuò)誤的所有單元格。Sub HighlightMisspelledCells()  Dim cl As Range  For Each cl In ActiveSheet.UsedRange   If Not Application.CheckSpelling(word:=cl.Text) Then   cl.Interior.Color = vbRed   End If Next cl  End Sub

請(qǐng)注意，突出顯示的單元格包含Excel認(rèn)為是拼寫(xiě)錯(cuò)誤的文本。當(dāng)然在許多情況下，它也會(huì)其它各種錯(cuò)誤。

18.刷新工作簿中的所有透視表

如果工作簿中有多個(gè)透視表，則可以使用此代碼一次刷新所有這些透視表。Sub RefreshAllPivotTables()  Dim PT As PivotTable   For Each PT In ActiveSheet.PivotTables  PT.RefreshTable   Next PT End Sub

19.將所選單元格的字母大小寫(xiě)改為大寫(xiě)

雖然Excel有更改文本字母大小寫(xiě)的公式，但它使您可以在另一組單元格中進(jìn)行更改。

使用此代碼可以立即更改所選文本中文本的字母大小寫(xiě)。Sub ChangeCase()  Dim Rng As Range  For Each Rng In Selection.Cells   If Rng.HasFormula = False Then   Rng.Value = UCase(Rng.Value)   End If Next Rng  End Sub

注意，在本例中，使用了UCase將文本大小寫(xiě)設(shè)為大寫(xiě)。

20.突出顯示有批注的單元格

使用下面的代碼突出顯示其中包含注釋的所有單元格。Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub

在本例中，使用vblue為單元格賦予藍(lán)色。如果你想的話，你可以把這個(gè)換成其他顏色。

21.突出顯示所選數(shù)據(jù)集中的空白單元格

雖然可以使用條件格式或“轉(zhuǎn)到特殊”對(duì)話框突出顯示空白單元格，但如果必須經(jīng)常這樣做，最好使用宏。

創(chuàng)建后，你可以將代碼保存在個(gè)人宏工作簿中。Sub HighlightBlankCells()  Dim Dataset As Range  Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed  End Sub

在這個(gè)代碼中，指定了紅色單元格中要突出顯示的空白單元格。

22.按單列對(duì)數(shù)據(jù)排序

可以使用下面的代碼按指定列對(duì)數(shù)據(jù)排序。Sub SortDataHeader()  Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes  End Sub

請(qǐng)注意，我創(chuàng)建了一個(gè)名為“datarange”的命名范圍，并使用它來(lái)代替單元格引用。

這里還使用了三個(gè)關(guān)鍵參數(shù)：參照之前的文章

23.按多列對(duì)數(shù)據(jù)排序

下面是將根據(jù)多個(gè)列對(duì)數(shù)據(jù)排序的代碼（A列先排序，在進(jìn)行B列排序）。Sub SortMultipleColumns()  With ActiveSheet.Sort   .SortFields.Add Key:=Range("A1"), Order:=xlAscending   .SortFields.Add Key:=Range("B1"), Order:=xlAscending   .SetRange Range("A1:C13")   .Header = xlYes .Apply End With  End Sub

注意，這個(gè)代碼指定了首先根據(jù)A列排序，然后根據(jù)B列排序

24.如何只從字符串中獲取數(shù)字部分

如果只從字符串中提取數(shù)字部分或文本部分，則可以在VBA中創(chuàng)建自定義函數(shù).

然后，您可以在工作表中使用這個(gè)vba函數(shù)（就像普通的Excel函數(shù)一樣），它將只從字符串中提取數(shù)字或文本部分.

下面是將創(chuàng)建函數(shù)從字符串中提取數(shù)字部分的VBA代碼:Function GetNumeric(CellRef As String)  Dim StringLength As Integer  StringLength = Len(CellRef)   For i = 1 To StringLength   If IsNumeric(Mid(CellRef, i, 1)) Then  Result = Result & Mid(CellRef, i, 1)   End If  Next i  GetNumeric = Result  End Function

您需要將代碼放入模塊中，然后可以在工作表中使用函數(shù)"=GetNumeric".

此函數(shù)只接受一個(gè)參數(shù)，即要從中獲取數(shù)值部分的單元格的單元格引用。

25.總是在激活特定選項(xiàng)卡的情況下打開(kāi)工作簿

如果要打開(kāi)一個(gè)工作簿，該工作簿總是在特定工作表的情況下打開(kāi)，則可以使用以下代碼。

當(dāng)您希望在工作簿打開(kāi)時(shí)激活指定工作表時(shí)，這將非常有用。Private Sub Workbook_Open()  Sheets(“Sheet1”).Select  End Sub

請(qǐng)注意，此代碼需要放在ThisWorkbook對(duì)象的“代碼”窗口中

這意味著當(dāng)您在VB編輯器中時(shí)，需要雙擊此工作簿對(duì)象并復(fù)制粘貼其中的代碼。

26.一次保存并關(guān)閉所有工作簿

如果有許多工作簿打開(kāi)，并且要保存和關(guān)閉這些工作簿，則需要手動(dòng)轉(zhuǎn)到并保存每個(gè)工作簿，然后關(guān)閉它。

這是一個(gè)VBA代碼，它將關(guān)閉所有工作簿并在關(guān)閉時(shí)保存它。Sub CloseAllWorkbooks()  Dim wb As Workbook   For Each wb In Workbooks   wb.Close SaveChanges:=True   Next wb  End Sub

請(qǐng)注意，代碼只適用于那些先前已經(jīng)保存過(guò)的工作簿。如果有新工作簿，則必須指定要保存該工作簿的文件夾的名稱(chēng)和位置。

27.限制光標(biāo)在特定區(qū)域的移動(dòng)

如果要限制工作表中的滾動(dòng)區(qū)域，可以使用以下代碼執(zhí)行此操作：Private Sub Worksheet_Open()  Sheets(“Sheet1”).ScrollArea = “A1:M17”  End Sub

請(qǐng)注意，您需要將此代碼放入要限制滾動(dòng)的工作表中。

28.將篩選后的數(shù)據(jù)復(fù)制到新工作簿中

如果您使用的是一個(gè)巨大的數(shù)據(jù)區(qū)域，那么過(guò)濾器在分割數(shù)據(jù)時(shí)非常有用。

有時(shí)，您可能只需要數(shù)據(jù)區(qū)域的一部分。

在這種情況下，您可以使用下面的代碼將篩選后的數(shù)據(jù)快速?gòu)?fù)制到新工作表中。Sub CopyFilteredData()   If ActiveSheet.AutoFilterMode = False Then   Exit Sub   End If  ActiveSheet.AutoFilter.Range.Copy  Workbooks.Add.Worksheets(1).Paste  Cells.EntireColumn.AutoFit  End Sub

此代碼首先檢查是否有任何已篩選的數(shù)據(jù)

否則，它會(huì)復(fù)制篩選后的數(shù)據(jù)，插入新工作簿，并將數(shù)據(jù)粘貼到其中。

29.將所有公式轉(zhuǎn)換為選定數(shù)據(jù)集中的值

如果要快速將所有具有公式的單元格轉(zhuǎn)換為值，可以使用以下代碼：Sub ConvertFormulastoValues()  Dim MyRange As Range Dim MyCell As Range  Set MyRange = Selection   For Each MyCell In MyRange   If MyCell.HasFormula Then   MyCell.Formula = MyCell.Value   End If   Next MyCell  End Sub

注意這個(gè)變化是不可逆的，公式將無(wú)法恢復(fù)。

或者，你也可以編寫(xiě)一個(gè)消息框，顯示公式將丟失的警告。這可以防止用戶(hù)意外運(yùn)行此宏

30.在單個(gè)單元格中獲取多個(gè)查找值

如果要查找表中的值并在同一單元格中獲取所有匹配結(jié)果，則需要使用VBA創(chuàng)建自定義函數(shù)。

下面是創(chuàng)建了一個(gè)公式，類(lèi)似VLOOKUP。Function GetMultipleLookupValues(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)  Dim i As Long  Dim Result As String   For i = 1 To LookupRange.Columns(1).Cells.Count   If LookupRange.Cells(i, 1) = Lookupvalue Then   Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & ","   End If  Next i GetMultipleLookupValues = Left(Result, Len(Result) – 1) End Function

注意，這個(gè)函數(shù)有三個(gè)參數(shù)：

LookupValue –需要查詢(xún)的值

LookupRange – 需要查詢(xún)的區(qū)域

ColumnNumber – 提取結(jié)果的列號(hào)