VBA遍歷行，選擇其中幾個然后洗掉它們

我有一個資料集，我想在其中洗掉它的每 x 行（x = userinput）。如果我立即洗掉行，最終結果將不正確，因為每次洗掉都會更改行順序。到目前為止，我寫了這段代碼：

Sub Delete_Data()
    'Take userinput
    Dim userInput As Variant
    Dim i As Long
    Do While True
        userInput = InputBox("please enter a number between 2-100", _
        "Lets delete some data XD")
        If IsNumeric(userInput) And userInput >= 2 _
        And userInput <= 100 Then
            Exit Do
        End If
        If MsgBox("Invalid Input, please redo or cancel", _
        vbOKCancel, "Invalid input") = vbCancel Then Exit Do
    Loop
    
    'Delete Rows
    Worksheets("Sheet1").Activate
    For i = 2 To Rows.count Step userInput
        If Rows.Cells(i, 1).Value = "" Then
            Rows(ActiveCell.Row).EntireRow.Delete
            MsgBox "you have successfully deleted every " _
            & userInput & "th row!"
            Exit For
        Else
            Rows(i).EntireRow.Select
        End If
    Next i
     
End Sub

問題是，一旦選擇了新行，先前選擇的行就會消失。我希望你們能幫助我。

uj5u.com熱心網友回復：

使用聯合，您的代碼將如下所示：

Sub Delete_Data()
    'Take userinput
    Dim userInput As Variant
    Dim i As Long
    Do While True
        userInput = InputBox("please enter a number between 2-100", _
        "Lets delete some data XD")
        If IsNumeric(userInput) And userInput >= 2 _
        And userInput <= 100 Then
            Exit Do
        End If
        If MsgBox("Invalid Input, please redo or cancel", _
        vbOKCancel, "Invalid input") = vbCancel Then Exit Do
    Loop
    
    'Delete Rows
    With Worksheets("Sheet1")
        Dim delrng As Range
        For i = 2 To .Cells(.Rows.Count, 2).End(xlUp).Row Step userInput 'Change 2 to whatever column has the most data
            If .Cells(i, 1).Value = "" Then
                If delrng Is Nothing Then
                    Set delrng = .Cells(i, 1).EntireRow
                Else
                    Set delrng = Union(delrng, .Cells(i, 1).EntireRow)
                End If
            End If
        Next i
    End With
        delrng.Delete
        Select Case True
            Case Right(userInput, 1) = 1 And Not userInput = 11
                MsgBox "you have successfully deleted every " _
                    & userInput & "st row!"
            Case Right(userInput, 1) = 2 And Not userInput = 12
                MsgBox "you have successfully deleted every " _
                    & userInput & "nd row!"
            
            Case Right(userInput, 1) = 3 And Not userInput = 13
                MsgBox "you have successfully deleted every " _
                    & userInput & "rd row!"
            Case Else
                MsgBox "you have successfully deleted every " _
                    & userInput & "th row!"
        End Select
End Sub

Msgbox我根據數字擴展了你的正確連接。

uj5u.com熱心網友回復：

感謝您的投入：

這就是最終的代碼

Option Explicit

Sub Delete_Data()
    'Take userinput
    Dim userInput As Variant
    Dim i As Long
    Do While True
        userInput = InputBox("please enter a number between 2-100", _
        "Lets delete some data XD")
        If IsNumeric(userInput) And userInput >= 2 _
        And userInput <= 100 Then
            Exit Do
        End If
        If MsgBox("Invalid Input, please redo or cancel", _
        vbOKCancel, "Invalid input") = vbCancel Then Exit Sub
    Loop
    
    'Activate rows "to be deleted"
    With Worksheets("Sheet1")
        Dim delRange As Range
        For i = 2 To .Cells(.Rows.count, 2).End(xlUp).Row Step userInput
            If .Cells(i, 1).Value = "" Then
                Exit For
            ElseIf delRange Is Nothing Then
                Set delRange = .Cells(i, 1).EntireRow
            Else
                Set delRange = Union(delRange, .Cells(i, 1).EntireRow)
            End If
        Next i
    End With
    
    'Mark rows "to be deleted"
    delRange.Interior.ColorIndex = 6
    
    'Ask for deltetion cofirmation
    Dim answer As Variant
    answer = MsgBox("Do you really want to delete the selected rows?", _
    vbQuestion   vbYesNo   vbDefaultButton2, "Confirm deletion")
    If answer = vbYes Then
        delRange.Delete
    Else
        delRange.Interior.ColorIndex = xlNone
        Exit Sub
    End If
    
    'Give feedback to user & look for correct english wording
    Select Case True
            Case Right(userInput, 1) = 1 And Not userInput = 11
                MsgBox "you have successfully deleted every " _
                    & userInput & "st row!"
            Case Right(userInput, 1) = 2 And Not userInput = 12
                MsgBox "you have successfully deleted every " _
                    & userInput & "nd row!"
            
            Case Right(userInput, 1) = 3 And Not userInput = 13
                MsgBox "you have successfully deleted every " _
                    & userInput & "rd row!"
            Case Else
                MsgBox "you have successfully deleted every " _
                    & userInput & "th row!"
        End Select
End Sub

標籤：vba for循环

上一篇：如何使用VBAADODB連接來連接已關閉的作業簿以獲取帶有字符\的命名范圍

下一篇：如何在MSAccess中使按鈕永遠變灰