VBA For循环未退出 [英] VBA For loop not exiting

问题描述

我正在遍历表中的行，如果不满足某些条件，则删除行.由于某种原因，即使完成，我的for循环也永远不会退出.我在做什么错了?

I am looping through rows of a table and deleting rows if certain conditions are not met. For some reason my for loop never exits even when its done. What am I doing wrong?

lastr = Range("a2").End(xlDown).Row
For r = 2 To lastr
    If Cells(r, 1).Value <> "SHORT POSITIONS" And Cells(r, 7).Value = 0 And Cells(r, 10).Value <> "Yes" Then
        Rows(r).Delete
        r = r - 1
        lastr = lastr - 1
    End If
Next r

推荐答案

在删除行时，总是从底部开始，朝顶部移动.无法从底部到顶部工作，将导致跳过的行，因为删除行后将重置行的位置.

ALWAYS start at the bottom and work towards the top when deleting rows. Failing to work from the bottom to the top will result in skipped rows as the position of the rows are reset after the row deletion.

切勿在对于...下一条语句中重置计数器.更改r会使事情变得混乱.更改lastr无效.进入循环时，它仍将保留为原始值的lastr.

NEVER reset your counter in a For ... Next Statement. Changing r mucks things up. Changing lastr has no effect. It will still go to the lastr that was the original value when you entered the loop.

lastr = Range("a" & ROWS.COUNT).End(xlUP).Row
For r = lastr To 2 STEP -1   '<~~ VERY IMPORTANT
    If Cells(r, 1).Value <> "SHORT POSITIONS" And Cells(r, 7).Value = 0 And Cells(r, 10).Value <> "Yes" Then
        Rows(r).Delete
    End If
Next r

通常，最好从下往上查找最后一个填充的单元格，

TYPICALLY, it is better to look for the last populated cell from the bottom up,

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