【office编程】VB删除指定excel中行

打开 Excel 中的 Visual Basic 编辑器，键入以下代码：

Sub BatchDelete()
    Dim inputstr As String
    Dim result() As String
    Dim sheet_no() As String
    Dim start_end() As String
    Dim i, n, num, WS_Count As Integer
    Dim ws As Worksheet
    
    inputstr = InputBox("Please input lines what you want batch delete(split by ',').")
    result = Split(inputstr, ",")

    For i = LBound(result) To UBound(result)
        If InStr(1, result(i), ":", vbTextCompare) <= 0 Then
            ' not containing :
            result(i) = result(i) & ":" & result(i)
        End If
    Next
    inputstr = Join(result, ",")

    inputstr2 = InputBox("Please input sheets what you want batch delete(split by ',').", Title:="Batch delete in sheets.", Default:="1:" & ActiveWorkbook.Worksheets.Count)
    sheet_no = Split(inputstr2, ",")

    For i = LBound(sheet_no) To UBound(sheet_no)
        If InStr(1, sheet_no(i), ":", vbTextCompare) > 0 Then
            ' containing :
            start_end = Split(sheet_no(i), ":")
            sheet_no(i) = start_end(0)
            For n = start_end(0) + 1 To start_end(1)
                sheet_no(i) = sheet_no(i) & "," & n
            Next
        End If
    Next
    inputstr2 = Join(sheet_no, ",")
    sheet_no = Split(inputstr2, ",")

    WS_Count = ActiveWorkbook.Worksheets.Count
    For n = LBound(sheet_no) To UBound(sheet_no)
        num = CInt(sheet_no(n))
        If num <= WS_Count Then
            Set ws = ActiveWorkbook.Worksheets(num)
            ws.Range(inputstr).Delete
        End If
    Next

End Sub

点击【宏】选择【BatchDelete】之后会有两次弹窗：

第一次弹窗：

填写需要删除的行号，逗号分隔，同时也支持用英文冒号连接数字来表示序号范围，如 3,5,7:11

第二个弹窗：

填写要处理的工作表的Sheet序号，从左往右依次为 1, 2, 3, ...
多个序号用英文逗号间隔，同时也支持用英文冒号连接数字来表示序号范围，如 3,5,7:11