Excel：“运行时错误-2147417848 (80010108)”方法“删除”对象‘范围’失败
Excel：“运行时错误-2147417848 (80010108)”方法“删除”对象‘范围’失败
提问于 2018-04-30 20:20:41
我有一个宏，它删除Excel中受保护的工作表中选定的表行，并在运行代码时接收标题中的错误。当我在工作表中引入10条条件格式规则时，就会出现这个问题，如果我清除任何条件格式的工作表，我可以删除任意多行。我的代码删除下面的表行，以及调试指向的位置以清除错误。
Sub DeleteRow()
Application.EnableEvents = False
Application.ScreenUpdating = False
Sheet1.Unprotect Password:="Password!"
Dim rng As Range
On Error Resume Next
With Selection.Cells(1)
Set rng = Intersect(.EntireRow, ActiveCell.ListObject.DataBodyRange)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "Please select a valid table cell.", vbCritical
Else
rng.Delete xlShiftUp 'This is the line where the debug is pointing to
End If
End With
Sheet1.Protect Password:="Password!"
Application.EnableEvents = True
End Sub谢谢你的帮忙!
*更新编号#参考资料！任何条件格式公式上的错误。**更新一有大约10个条件格式公式，下面略有变化。
=AND(COUNTIFS($E$14:$E$17,$E14,$J$14:$J$17,"Black")>1,$J14="Black")
=AND(COUNTIFS($E$14:$E$17,$E14,$J$14:$J$17,"White")>1,$J14="White")
=AND(COUNTIFS($E$14:$E$17,$E14,$J$14:$J$17,"Green")>1,$J14="Green")回答 1
Stack Overflow用户
发布于 2018-04-30 20:37:11
最可能的条件格式是某个地方出现了#REF!错误，因此Excel不允许删除。要查看条件格式中的所有公式，请运行以下命令：
Sub ListAllConditionalFormat()
Dim cf As FormatCondition
Dim ws As Worksheet
Dim l As Long
Dim rngCell As Range
On Error Resume Next
Application.EnableEvents = False
Application.ScreenUpdating = False
Worksheets("Report").Cells.Clear
For Each ws In ThisWorkbook.Worksheets
Debug.Print ws.Name
For Each cf In ws.Cells.FormatConditions
l = 1 + l
With Worksheets("Report")
Set rngCell = .Cells(l, 1)
rngCell = cf.AppliesTo.Address
rngCell.Offset(0, 1) = cf.Type
rngCell.Offset(0, 2) = "'" & cf.Formula1
rngCell.Offset(0, 3) = cf.Interior.Color
rngCell.Offset(0, 4) = cf.Font.Name
rngCell.Offset(0, 5) = ws.Name
rngCell.Offset(0, 6) = "'" & cf.AppliesTo.AddressLocal
rngCell.Offset(0, 7) = "'" & cf.Formula2
End With
Next cf
Next ws
Debug.Print "END!"
End Sub只需确保您有一个名为Report的工作表，其中所有内容都是空的。条件格式的信息将在那里。查找#REF!错误，并在看到它们后修复它们。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50107644
复制相关文章
相似问题
腾讯云开发者
