删除选定的行 [英] Deleting a selected row

问题描述

我有以下代码，可有效删除指定工作表中的三行(查找范围等).我用了至少一年的这段代码没有任何问题.但是，我最近已将此代码转移到新的工作簿中，并建立了一个与以前的工作簿完全相同的工作表.

I have the below code which effectively deletes three lines within a designated worksheet (Finding ranges etc). This code I have used for atleast a year without any issues. However I have recently transferred this code to a new work book and set up a worksheet which is exactly the same as the previous workbook.

代码错误，下面以错误消息突出显示

The code errors where highlighted below with the error message

运行时错误'1004'

Run-Time error '1004'

删除范围类的方法失败

任何人都可以建议为什么会发生此错误吗?

Can anyone suggest why this error would occur?

Sub DeleteRowPIC()

Application.ScreenUpdating = False
Application.Calculation = xlManual
ActiveSheet.Unprotect Password:="Projects123"
ActiveSheet.Range("Total").Select
If Selection.Row = 12 Then
Else
ActiveSheet.Range("Total").Select
Selection.Offset(-2, 0).Select
ActiveCell.EntireRow.Delete

ActiveSheet.Range("Total_1").Select
Selection.Offset(-2, 0).Select
ActiveCell.EntireRow.Delete **ERROR OCCURS HERE**

ActiveSheet.Range("Total_2").Select
Selection.Offset(-2, 0).Select
ActiveCell.EntireRow.Delete

End If
Range("K2").Select
Application.Calculation = xlAutomatic
With ActiveSheet

      .Protect Password:="Projects123", UserInterfaceOnly:=True
      .EnableOutlining = True
End With

推荐答案

如注释中所述，在删除行时，您应始终从最后一行开始，一直到第一行.我怀疑您的问题是由此引起的.在看不到您的数据的情况下，建议您从 Total_2 返回到 Total .另外，应尽可能避免使用 Select .尝试将您的代码修改为此:

As mentioned in the comments, you should always start from the last row and work your way to the first row when deleting rows. I suspect your issue is caused by this. Without seeing your data, I would suggest working from Total_2 back to Total. Also, you should avoid using Select whenever possible. Try modifying your code to this:

Sub DeleteRowPIC()

Dim ws As Worksheet

Application.ScreenUpdating = False
Application.Calculation = xlManual

Set ws = ActiveSheet
With ws
    .Unprotect Password:="Projects123"
    If .Range("Total").Row <> 12 Then
        .Range("Total_2").Offset(-2, 0).EntireRow.Delete
        .Range("Total_1").Offset(-2, 0).EntireRow.Delete
        .Range("Total").Offset(-2, 0).EntireRow.Delete
    End If
    Application.Calculation = xlAutomatic
    .Protect Password:="Projects123", UserInterfaceOnly:=True
    .EnableOutlining = True
End With

确保在某些时候也将 Application.ScreenUpdating 设置为true.

Make sure you set Application.ScreenUpdating back to true at some point as well.

这篇关于删除选定的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！