excel - 删除按钮以删除相关行和这些行中的所有按钮

Question

我创建了这个删除按钮，它触发删除相对范围的行，包括删除行中的所有按钮。有时该代码不会运行，它会给我运行时 1004 错误“应用程序定义的或对象定义的错误”，它会突出显示 If Not Intersect 语句。有时它会正常工作。看起来这取决于我在点击底部之前所做的事情。某些东西正在触发错误。请帮忙！

 Sub Delete_Button()
    'Delete_Button

'Step 1: Select the cell under the clicked button

    Dim r As Range
    Dim s As Shape

    Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
    Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column)).Select

'Step 2: delete all buttons relative to the selected cell from step 1

    StartCell = ActiveCell.Offset(-5, 0).Address
    EndCell = ActiveCell.Offset(0, 0).Address

    For Each s In ActiveSheet.Shapes
    If Not Intersect(Range(StartCell, EndCell), s.TopLeftCell) Is Nothing Then
            s.Delete
        End If
    Next s

'Step 3: delete the rows relative to the selected cell from step 1

        ActiveCell.Offset(-7, 0).Rows("1:8").EntireRow.Select
        Selection.Delete Shift:=xlUp
        ActiveCell.Offset(-4, 0).Range("A1").Select

    End Sub

标签： excelvbabutton