如何在 Excel 中隱藏特定的錯(cuò)誤值？

Updated by ExtendOffice 20220824 Dim xRg As Range Dim xFindStr As String Dim xFindRg As Range Dim xARg As Range Dim xURg As Range Dim xFindRgs As Range Dim xFAddress As String Dim xBol As Boolean Dim xJ xArrFinStr = Array("#DIV/0!”, “#N/A”, “#NAME?") Enter the errors to hide, enclose each with double quotes and separate them with commas On Error Resume Next Set xRg = Application.InputBox("Please select the range that includes the errors to hide:", "Kutools for Excel", , Type:=8) If xRg Is Nothing Then Exit Sub xBol = False For Each xARg In xRg.Areas Set xFindRg = Nothing Set xFindRgs = Nothing Set xURg = Application.Intersect(xARg, xARg.Worksheet.UsedRange) For Each xFindRg In xURg For xJ = LBound(xArrFinStr) To UBound(xArrFinStr) If xFindRg.Text = xArrFinStr(xJ) Then xBol = True If xFindRgs Is Nothing Then Set xFindRgs = xFindRg Else Set xFindRgs = Application.Union(xFindRgs, xFindRg) End If End If Next Next If Not xFindRgs Is Nothing Then xFindRgs.Font.ThemeColor = xlThemeColorDark1 End If Next If xBol Then MsgBox "Successfully hidden." Else MsgBox "No specified errors were found." End If End Sub

Sub HideSpecificErrors_WorkSheets() Updated by ExtendOffice 20220824 Dim xRg As Range Dim xFindStr As String Dim xFindRg As Range Dim xARg, xFindRgs As Range Dim xWShs As Worksheets Dim xWSh As Worksheet Dim xWb As Workbook Dim xURg As Range Dim xFAddress As String Dim xArr, xArrFinStr Dim xI, xJ Dim xBol As Boolean xArr = Array("Sheet1", "Sheet2") Names of the sheets where to find and hide the errors. Enclose each with double quotes and separate them with commas xArrFinStr = Array("#DIV/0!", "#N/A", "#NAME?") Enter the errors to hide, enclose each with double quotes and separate them with commas On Error Resume Next Set xWb = Application.ActiveWorkbook xBol = False For xI = LBound(xArr) To UBound(xArr) Set xWSh = xWb.Worksheets(xArr(xI)) Set xFindRg = Nothing xWSh.Activate Set xFindRgs = Nothing Set xURg = xWSh.UsedRange Set xFindRgs = Nothing For Each xFindRg In xURg For xJ = LBound(xArrFinStr) To UBound(xArrFinStr) If xFindRg.Text = xArrFinStr(xJ) Then xBol = True If xFindRgs Is Nothing Then Set xFindRgs = xFindRg Else Set xFindRgs = Application.Union(xFindRgs, xFindRg) End If End If Next Next If Not xFindRgs Is Nothing Then xFindRgs.Font.ThemeColor = xlThemeColorDark1 End If Next If xBol Then MsgBox "Successfully hidden." Else MsgBox "No specified errors were found." End If End Sub

請(qǐng)注意： 在片段中 xArr = Array("Sheet1", "Sheet2") 在第 15 行，你應(yīng)該替換 “表 1”、“表 2” 使用要隱藏錯(cuò)誤的工作表的實(shí)際名稱。 請(qǐng)記住用雙引號(hào)將每個(gè)工作表名稱括起來(lái)，并用逗號(hào)分隔它們。 在片段中 xArrFinStr = Array("#DIV/0!", "#N/A", "#NAME?") 在第 16 行，你應(yīng)該替換 “#DIV/0！”、“#N/A”、“#NAME？” 對(duì)于要隱藏的實(shí)際錯(cuò)誤，請(qǐng)記住用雙引號(hào)將每個(gè)錯(cuò)誤括起來(lái)并用逗號(hào)分隔它們。