Delete complete rows in Excel VBA containing specific data

Question

I am trying to delete complete row which contain the value "THD" in first cell. The content could look like "THD_C_L10.0W5.0H15.0P7.5" but THD is always there.

I tried it with following loop:

For i = 1 To ActiveSheet.UsedRange.Rows.Count
    If InStr(1, LCase(Tabelle2.Cells(i, 1)), "THD") <> 0 Then
        Rows(i).EntireRow.Delete
    End If
Next

But unfortunately its not working. Any ideas?

Kazimierz Jawor · Accepted Answer · 2013-04-15 07:21:42Z

2

When you delete rows you have to start your loop from last element and direct into first. Therefore, instead of:

For i = 1 To ActiveSheet.UsedRange.Rows.Count

you should have something like this:

For i = ActiveSheet.UsedRange.Rows.Count to 1 Step -1

One additional tip which shouldn't affect your current sub- InStr has additional parameter which you could use. This line is equivalent to yours one:

If InStr(1, Tabelle2.Cells(i, 1), "THD",vbTextCompare) <> 0 Then

edited Apr 15, 2013 at 7:21

answered Apr 15, 2013 at 6:36

Kazimierz Jawor

19.1k7 gold badges38 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete complete rows in Excel VBA containing specific data

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related