时间戳

首页 > 解决方案 > Excel VBA 在某些情况下不会删除行

excel - Excel VBA 在某些情况下不会删除行

问题描述

我正在使用以下代码删除 excel 中的可见行（在过滤大于 30 的值之后）。我面临的问题是代码工作正常并在值（大于30）按顺序时删除行，但它不会删除行并且当值不按顺序时会出错。我究竟做错了什么？

'With rng
     .AutoFilter Field:=13, Criteria1:=">30"
     .SpecialCells(xlCellTypeVisible).EntireRow.Delete
     .AutoFilter Field:=13, Criteria1:=">0"
End With '

我得到错误：Delete method of Range class failed

这就是我要删除的数据的样子：

在此处输入图像描述

如何设置范围：

Dim rng As Range

LastRow = btsvoice.Range("M" & btsvoice.Rows.Count).End(xlUp).Row
Set rng = btsvoice.Range("M2:M" & LastRow)

下面是整个模块代码：

Sub Delete_Row_Final()

    Dim voice As Workbook
    Dim data As Workbook
    Dim btsvoice As Worksheet
    Dim nodebvoice As Worksheet
    Dim btsdata As Worksheet
    Dim enodebdata As Worksheet
    Dim rng As Range
    Dim LastRow As Long
    Dim MyRange As Range

    Set voice = Workbooks("Voice_Files.xlsx")
    Set btsvoice = voice.Sheets("2G Voice")
    Set nodebvoice = voice.Sheets("3G Voice")

    Set data = Workbooks("Data_Files.xlsx")
    Set btsdata = data.Sheets("2G Data")
    Set enodebdata = data.Sheets("4G Data")

    Application.Calculate
    If Not Application.CalculationState = xlDone Then
        DoEvents
    End If

    ' -------------------  2G Voice ------------------------
    'filter and delete all but header row which is in row 3
LastRow = btsvoice.Range("M" & btsvoice.Rows.Count).End(xlUp).Row
Set rng = btsvoice.Range("M1:M" & LastRow)

    ' filter and delete all but header row
    With rng
         .AutoFilter Field:=13, Criteria1:=">30"
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter Field:=13, Criteria1:=">0"
    End With

    ' -------------------  3G Voice ------------------------
    'filter and delete all but header row which is in row 3
    LastRow = nodebvoice.Range("K" & nodebvoice.Rows.Count).End(xlUp).Row
    Set rng = nodebvoice.Range("K2:K" & LastRow)

    ' filter and delete all but header row
    With rng
         .AutoFilter Field:=11, Criteria1:=">30"
         .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
         .AutoFilter Field:=11, Criteria1:=">0"
    End With

    voice.Save

        ' -------------------  2G Data ------------------------
    'filter and delete all but header row which is in row 3
    LastRow = btsdata.Range("L" & btsdata.Rows.Count).End(xlUp).Row
    Set rng = btsdata.Range("L2:L" & LastRow)

    ' filter and delete all but header row
    With rng
         .AutoFilter Field:=12, Criteria1:=">30"
         .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
         .AutoFilter Field:=12, Criteria1:=">0"
    End With

            ' -------------------  4G Data ------------------------
    'filter and delete all but header row which is in row 3
    LastRow = enodebdata.Range("M" & enodebdata.Rows.Count).End(xlUp).Row
    Set rng = enodebdata.Range("M2:M" & LastRow)

    ' filter and delete all but header row
    With rng
         .AutoFilter Field:=13, Criteria1:=">30"
         .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
         .AutoFilter Field:=13, Criteria1:=">0"
    End With

    data.Save

End Sub

标签： excelvba

解决方案

3查看您当前的问题和解释，我认为您的代码中一定有额外的内容：

如果设置范围：

LastRow = btsvoice.Range("M" & btsvoice.Rows.Count).End(xlUp).Row
Set rng = btsvoice.Range("M2:M" & LastRow)

您无法索引，Field:=13因为它根本不存在于 1 列范围内。改为使用Field:=1。

但是，要提出的其他一些建议是，由于您有标头，因此使用它可能会更好Offset，使您的代码看起来像：

Dim rng As Range

LastRow = btsvoice.Range("M" & btsvoice.Rows.Count).End(xlUp).Row
Set rng = btsvoice.Range("M1:M" & LastRow)

With rng
    .AutoFilter Field:=1, Criteria1:=">30"
    .Offset(1,0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    .AutoFilter Field:=1, Criteria1:=">0"
End With

AutoFilter我可以想象的另一件事：当已经应用到单个列上并且您想要另一个单个列时，也会抛出所描述的错误AutoFilter。这意味着您有两个选择：

  • AutoFilter在应用另一个过滤器之前删除
  • 将过滤器应用于整个范围而不是单个列

对于第一个选项，您可以尝试：

With Sheet1
    If .AutoFilterMode = True Then .AutoFilterMode = False
End with

对于第二个选项，您需要将范围设置为 A:M 列（甚至更远）

Set rng = btsvoice.Range("A2:M" & LastRow)

推荐阅读