反向循环删除行-VB [英] Deleting Rows with Reverse Loop - VB

问题描述

我有一个数据集，其中包含有关客户的大量信息.我要在 K 行的单元格中输入 Yes ，并将包含要从第一个工作表

我通过按下按钮来执行此操作.工作正常，并且行被删除，但是当然，当它删除行时，行号向上移动一位，因此下一行被跳过(删除行15，然后行16变为行15，依此类推)，所以我想出了一个反向循环是可行的方法，但是我无法一生解决这个问题-我认为这很简单！这是我正在使用的代码:

  Private Sub UpdateSheet2_Click()调光范围昏暗的来源作为工作表昏暗目标作为工作表'根据需要更改工作表名称设置源= ActiveWorkbook.Worksheets("Sheet1")设置目标= ActiveWorkbook.Worksheets("Sheet2")对于Source.Range("K:K")中的每个c'覆盖所有行如果c =是"，则Source.Range("A"& c.Row&:B"& c.Row).Copy Target.Range("A"& Rows.Count).End(xlUp).Offset(1，0)Source.Range("D"& c.Row&:F"& c.Row).Copy Target.Range("C"& Rows.Count).End(xlUp).Offset(1，0)Source.Range("H"& c.Row&:J"& c.Row).Copy Target.Range("F"& Rows.Count).End(xlUp).Offset(1，0)万一'如果c =是"，则'Source.Rows(c.Row).EntireRow.Delete'        万一下一个c对于i = 500至1步骤-1如果Source.Range("K"& i)=是"，则Source.Rows(i.Row).EntireRow.Delete万一接下来我结束子 

我现在只是将行号设置为1到500之间，当基本功能正常运行时，我将更改它.您可以在For Each循环中看到我注释掉原始删除方法的位置.我目前在 Source.Rows(i.Row).EntireRow.Delete

行上收到错误"Object required"

解决方案

通常一次删除所有行的效率更高.

  Set c = Nothing'重置您的范围对于i = 1至500'或500至1步骤-1-没什么区别如果Source.Range("K"& i)=是"，则如果c什么都不是设置c = .Cells(i，1).EntireRow'如果范围为空，则将其设置为所需的行别的设置c = Union(c，.Cells(i，1)).EntireRow'否则添加此行万一万一下一个如果不是，则c.Delete xlUp'如果范围不为空，则删除所有行 

(我应该补充一点，我对 Cells(x，y).EntireRow 的使用纯粹是个人喜好.我总是使用该格式，因为我发现它更易于调试.)

I have a dataset that contains a bunch of information on customers. I want this information moved into another worksheet when Yes is entered into the Cell in row K, and the original row containing the information to be deleted from the first worksheet

I'm doing this with a button press. Works fine, and rows were being deleted, but of course when it deletes the row then the row numbers shift up by one and so the next row is skipped over (row 15 is deleted and then row 16 becomes row 15 etc.) so I figured a reverse loop is the way to go, but I can't for the life of me figure this out - I thought it would be simple! Here's the code I'm using:

Private Sub UpdateSheet2_Click()

Dim c As Range
Dim Source As Worksheet
Dim Target As Worksheet

    'Change worksheet designation as needed
    Set Source = ActiveWorkbook.Worksheets("Sheet1")
    Set Target = ActiveWorkbook.Worksheets("Sheet2")

    For Each c In Source.Range("K:K") 'Covers all rows          
        If c = "yes" Then
            Source.Range("A" & c.Row & ":B" & c.Row).Copy Target.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
            Source.Range("D" & c.Row & ":F" & c.Row).Copy Target.Range("C" & Rows.Count).End(xlUp).Offset(1, 0)
            Source.Range("H" & c.Row & ":J" & c.Row).Copy Target.Range("F" & Rows.Count).End(xlUp).Offset(1, 0)    
        End If

'        If c = "yes" Then
'            Source.Rows(c.Row).EntireRow.Delete
'        End If
    Next c

    For i = 500 To 1 Step -1
        If Source.Range("K" & i) = "yes" Then
            Source.Rows(i.Row).EntireRow.Delete
        End If  
    Next i 

End Sub

I'm just setting the row numbers between 1 and 500 for now, I will alter this when I have the basic functionality working. You can see where I commented out the original deletion method in the For Each loop. I'm currently getting the error "Object required" on the line Source.Rows(i.Row).EntireRow.Delete

解决方案

It is generally more efficient to delete all your rows at once.

Set c = Nothing 'reset your range
For i = 1 To 500 'or 500 To 1 Step -1 - doesn't make a difference
    If Source.Range("K" & i) = "yes" Then
        If c Is Nothing Then
            Set c = .Cells(i, 1).EntireRow 'if the range is empty then set it to the required row
        Else
            Set c = Union(c, .Cells(i, 1)).EntireRow 'otherwise add this row
        End If
    End If
Next
If Not c Is Nothing Then c.Delete xlUp 'if the range is not empty then delete all the rows

(I should add that my use of Cells(x,y).EntireRow is purely a personal preference. I always use that format as I find it easier to debug.)

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