Excel VBA中的For循环仅删除每第二行 [英] For loop in Excel VBA only deleting every 2nd row

问题描述

我以为我已经通过这里的答案解决了这个问题，但是我仍然只删除需要删除的第二行.循环为:

I thought I'd solved this problem with the help of answers here but I'm still only deleting every second row that needs deleting. The loop is:

For Each row In ActiveSheet.UsedRange.Rows
    'test for v in cell f and delete if there isn't one
    str = Cells(row.row, "F").Value
    Debug.Print "str is " & str
    If InStr(1, str, "V") <> 0 Then
        Debug.Print "hello str is " & str
    Else
        row.Delete Shift:=xlUp
    End If
Next row

但是当我在以下行上运行它时:

But when I run it on the following rows:

M 1301
M 1302
M 1401
ES 1501
M 1501
M 1502
MV 1502
M 1503
MV 1503

我最终得到:

M1301   PMH
M1401   Rod Washer
M1502   Rod Washer
MV1502  Rod Washer
MV1503  Rod Washer

我觉得我快疯了.我在循环中也有一个递增计数器，并认为这是问题所在，但是即使我已停止使用计数器来引用行，我仍然有它.

I feel like I'm going crazy. I had an incrementing counter in the loop as well and thought that was the problem but I still have it even though I've stopped using the counter to reference rows.

任何帮助指出我认为是显而易见的帮助将不胜感激.

Any help in pointing out what I assume is obvious would be much appreciated.

谢谢

推荐答案

尝试下面的代码，我尝试使用尽可能多的原始逻辑(即使有更简便的方法).

Try the code below, I tried to use as much of your original logic (even though there are easier and shorter ways to do it).

代码内部的注释说明

注意:通常，在删除 Objects 或 Rows 时，总是向后循环.

Note: in General, allways loop backwards when deleting Objects, or Rows in your case.

代码

Code

Option Explicit

Sub DeleteV()

Dim Rng As Range, i As Long, LastRow As Long
Dim Str As String

' I would rather use Worksheets("SheetName") instead
Set Rng = ActiveSheet.UsedRange

LastRow = Rng.Rows.Count + Rng.Row - 1 ' just in case your range starts from the 2nd row (or 3rd...)

' allways loop backwards when deleting rows
For i = LastRow To Rng.Row Step -1
    'test for v in cell f and delete if there isn't one
    Str = Cells(i, "F").Value
    Debug.Print "str is " & Str

    If InStr(1, Str, "V") <> 0 Then
        Debug.Print "hello str is " & Str
    Else
        Rows(i).Delete
    End If
Next i


End Sub

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