2019-04-04 11:45:00 浮雲Excel

26.一次保存並關閉所有工作簿

如果有許多工作簿打開，並且要保存和關閉這些工作簿，則需要手動轉到並保存每個工作簿，然後關閉它。

這是一個VBA代碼，它將關閉所有工作簿並在關閉時保存它。

Sub CloseAllWorkbooks() 
Dim wb As Workbook 
 For Each wb In Workbooks 
 wb.Close SaveChanges:=True 
 Next wb 
End Sub

請注意，代碼只適用於那些先前已經保存過的工作簿。如果有新工作簿，則必須指定要保存該工作簿的文件夾的名稱和位置。

27.限制光標在特定區域的移動

如果要限制工作表中的滾動區域，可以使用以下代碼執行此操作：

Private Sub Worksheet_Open() 
Sheets(“Sheet1”).ScrollArea = “A1:M17” 
End Sub

請注意，您需要將此代碼放入要限制滾動的工作表中。

28.將篩選後的數據複製到新工作簿中

如果您使用的是一個巨大的數據區域，那麼過濾器在分割數據時非常有用。

有時，您可能只需要數據區域的一部分。

在這種情況下，您可以使用下面的代碼將篩選後的數據快速複製到新工作表中。

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

此代碼首先檢查是否有任何已篩選的數據

否則，它會複製篩選後的數據，插入新工作簿，並將數據粘貼到其中。

29.將所有公式轉換為選定數據集中的值

如果要快速將所有具有公式的單元格轉換為值，可以使用以下代碼：

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

注意這個變化是不可逆的，公式將無法恢復。

或者，你也可以編寫一個消息框，顯示公式將丟失的警告。這可以防止用戶意外運行此宏

30.在單個單元格中獲取多個查找值

如果要查找表中的值並在同一單元格中獲取所有匹配結果，則需要使用VBA創建自定義函數。

下面是創建了一個公式，類似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

注意，這個函數有三個參數：

LookupValue –需要查詢的值

LookupRange – 需要查詢的區域

ColumnNumber – 提取結果的列號