删除行循环VBA [英] Delete row loop vba

问题描述

我在vba中有一个函数，它遍历一组数据并检查某些值是否存在，如果存在，请删除该行.它有效，但不会删除所有值.我很确定这是因为循环的上限是该范围的行数，每次删除都会变小.我将如何改变呢?任何帮助将不胜感激！

Function DeleteClients(rng1 As Range, rng2 As Range)

vData1 = rng1.Value

For i = 1 To rng2.Rows.Count

    For j = LBound(vData1, 1) To UBound(vData1, 1)

        If rng2.Cells(i, 1).Value = vData1(j, 1) Then
            rng2.Cells(i, 1).EntireRow.Delete
            Exit For
        End If

    Next j
Application.StatusBar = "Deleting out excluded clients... " & i & "/" & rng2.Rows.Count & " Records Processed " & Round((i / rng2.Rows.Count) * 100, 0) & " % Complete"
Next i

Application.StatusBar = False

End Function

解决方案

Kyle是正确的-通过步骤-1从底部(rng2.Rows.Count)循环到顶部(1)，这应该可以正常工作.

您正在做的是，当您删除第5行时，旧的第6行是新的第5行，但是下一个循环是在查看第6行-旧的第7行，因此您的旧第6行(新的第5行) )永远不会被考虑.从下到上循环可以解决此问题，因为如果删除第5行，则要考虑的下一行是第4行，并且它没有移动.

I have a function in vba that loops through a set of data and checks if certain values exist and if they do, delete the row. It works but it doesn't delete all the values. I'm pretty sure it is because the loop's upper bound is the row count of the range which with every deletion, gets smaller. How would I change this? Any help would be greatly appreciated!

Function DeleteClients(rng1 As Range, rng2 As Range)

vData1 = rng1.Value

For i = 1 To rng2.Rows.Count

    For j = LBound(vData1, 1) To UBound(vData1, 1)

        If rng2.Cells(i, 1).Value = vData1(j, 1) Then
            rng2.Cells(i, 1).EntireRow.Delete
            Exit For
        End If

    Next j
Application.StatusBar = "Deleting out excluded clients... " & i & "/" & rng2.Rows.Count & " Records Processed " & Round((i / rng2.Rows.Count) * 100, 0) & " % Complete"
Next i

Application.StatusBar = False

End Function

解决方案

Kyle is right -- loop from bottom (rng2.Rows.Count) to top (1) with step -1, and this should work fine.

What's doing you in is that when you delete row 5, the old row 6 is the new row 5, but the next loop is looking at row 6 -- the old Row 7, so your old Row 6 (new Row 5) never gets considered. Looping from bottom to top solves this, because if you delete Row 5, the next row to be considered is Row 4, and it hasn't moved.

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