CODEWITHSUNDEEP
excelvba
Hiddenllyy
Hiddenllyy

Reputation: 179

VBA How to delete the improper discrete lines

I tried to delete some lines whose sixth cell doesn't contain the correct value. And Here is my code:

dim i As Integer
for i = 1 to Row
    If Worksheets("Data").Cells(i,6).Text <>"a" Then
        Worksheets("Data").Rows(i).Delete
    End If
Next i

And I've found that if I delete line 1 (aaa), then the former line 2(bbb) becomes line 1, perhaps it's not the line that I want to keep, but i has already became 2, so actually I missed this line (bbb) and remove directly to the former line 3 (ccc).

1 aaa => bbb
2 bbb => ccc
3 ccc

And I don't know if there is a good algorithm for this problem? Thank you.

Upvotes: 0

Views: 58

Answers (4)

Arun Thomas
Arun Thomas

Reputation: 845

I would suggest you use the while loop so that you could reduce the iterations as well as the limit of the loop after deleting a row, see below

Dim i As Integer
Dim rows As Integer
row_count = 3
i = 1

While i <= row_count
    If Worksheets("Data").Cells(i, 6).Text <> "a" Then
        Worksheets("Data").rows(i).Delete
        i = i - 1
        row_count = row_count - 1
    End If
    i = i + 1
Wend

Upvotes: 1

Sathish Kothandam
Sathish Kothandam

Reputation: 1520

Let's do it in reverse order . try to modify the below code as your wish.

Sub Deleterows()
    Dim lRow As Long
    Dim iCntr As Long
    lRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row ' calculates no of rows used in A column

    For iCntr = lRow To 6 Step -1 ' loop in reverse order
        If Cells(iCntr, 1) = "str" Then 'You can change this text
            Sheets("Data").Rows(iCntr).Delete
        End If
    Next

    End Sub

Upvotes: 2

user6432984
user6432984

Reputation:

Always iterate backwards from last to first when deleting. 

dim i As Integer
for i = Row to 1 Step -1
    If Worksheets("Data").Cells(i,6).Text <>"a" Then
        Worksheets("Data").Rows(i).Delete
    End If
Next i

Upvotes: 3

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19837

If you're deleting rows it's best to work from the bottom up.
Change for i = 1 to Row to for i = Row to 1 Step -1.

Now when you delete a row it won't alter the row number of any rows above it.

Upvotes: 2

Related Questions