当使用"EntireRow.Delete"的任何变体时，Range类的删除方法失败. [英] Delete method of Range class failed, when using any variation of "EntireRow.Delete"

问题描述

我试图遍历使用范围的指定列中的所有单元格，并删除该值等于变量中指定的另一个值的所有行.但是，每当我尝试使用.EntireRow.Delete的某些变体时，都会收到错误消息:

I'm trying to loop through all the cells in a specified column of a used range and delete any rows where the value equals that of another value specified in a variable. However, anytime I try and use some variation of .EntireRow.Delete I get an error message:

运行时错误"1004":Range类的删除方法失败

Run-Time Error '1004': Delete method of Range class failed

我还记得这一天(Excel 2007)在工作，所以我有点困惑为什么它不再工作了.MS贬值了吗?似乎是一段有用的小代码，所以我看不出为什么会这么做.

I remember this working back in the day (Excel 2007) so I'm a little confused why it wouldn't work anymore. Did MS depreciate it? It seems like such a useful little piece of code so I can't see why they would.

无论如何，这是下面的代码，如果有人可以帮助我，我将不胜感激.

Anyways, here's the code below, if anyone can help I'd appreciate it.

Dim rngColumnRef As String
    rngColumnRef = "$A:$A"
Dim rngDeleteValue As String
    rngDeleteValue = "X"
    Set tempRange = ActiveSheet.UsedRange.Columns(rngColumnRef)
    For i = 1 To tempRange.Rows.Count
        If ActiveCell.Value = rngDeleteValue Then
            ActiveCell.EntireRow.Delete 'this is where I get the error
        Else
            ActiveCell.Offset(1, 0).Activate
        End If
    Next i

推荐答案

好像我在工作表中有一个未转换为范围的表(或 ListObject ).由于表格是我要删除的行的一部分，因此根本不允许我这样做.这是我用来更正此问题的修改后的代码(或其他代码):

Looks like I had a Table (or ListObject) in the sheet that wasn't converted to a range. Since the Table was part of the row I was trying to delete, it simply wouldn't let me do it. Here's the modified code (or additional code) I used to correct this:

'New Code

   Dim TableRange as ListObject 'Ideally I would place all my declarations 
                             'up here, but I wanted to show a very
                             'clear, Before and After picture of the code.

   For Each TableRange in ActiveSheet.ListObjects    
       TableRange.Unlist
   Next TableRange

'End of New Code    

Dim rngColumnRef As String
    rngColumnRef = "$A:$A"
Dim rngDeleteValue As String
    rngDeleteValue = "X"
    Set tempRange = ActiveSheet.UsedRange.Columns(rngColumnRef)
    For i = 1 To tempRange.Rows.Count
        If ActiveCell.Value = rngDeleteValue Then
            ActiveCell.EntireRow.Delete 'this is where I got the error
        Else
            ActiveCell.Offset(1, 0).Activate
        End If
    Next i

这篇关于当使用"EntireRow.Delete"的任何变体时，Range类的删除方法失败.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！