CODEWITHSUNDEEP
excelvba
Madeline
Madeline

Reputation: 39

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

I end up with: 

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.

Thanks

Upvotes: 1

Views: 387

Answers (4)

Michał Turczyn
Michał Turczyn

Reputation: 37367

I can suggest some changes in code, that will help:

'always store reference to the sheet in a variable!
Dim sh As Worksheet
Set sh = ActiveSheet
'determine last row in F column
lastRow = sh.Cells(sh.Rows.Count, 6).End(xlUp).Row

For i = lastRow To 1 Step -1
    'test for v in cell f and delete if there isn't one
    'we make it uppercase, to avoid situation that we didn't match v with V
    str = UCase(sh.Cells(i, 6).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

Upvotes: 1

Nicholas Bucher Ampudia
Nicholas Bucher Ampudia

Reputation: 41

The moment you erase a row, all following row indexes decrease by one.

This means, if you erase row n because it contains a "V", row n+1 is now row n and will not be tested.

Hence this, your code skips every second row.

To fix it, try going through the rows backwards.

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33682

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).

Explanation inside the code as comments,

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

Upvotes: 2

Dan Donoghue
Dan Donoghue

Reputation: 6206

Because you delete a row row.Delete Shift:=xlUp for example row 4, row 5 now becomes row 4 when you deleted the row and you then go to the next row (row 5 which is the old row 6).

You can either put Row = Row - 1 after your delete OR you could do it a different way which is to go backwards.

Example

For X = range("A" & rows.count).end(xlup).row to 2 step - 1
    'Do something per row
    'Delete a row if need be
Next

This should give you enough of an idea to fix this.

Upvotes: 3

Related Questions