Excel VBA - Delete Rows on certain conditions

Question

If a row has the value INACTIVE in column D and #N/A in column H, I want to delete that row.

I tried to achive that with my code below, but no row actually gets deleted.

Dim ws3 As Worksheet
Dim r As Integer
Set ws3 = ThisWorkbook.Sheets("Sheet2")
With ws3
For r = Sheet2.UsedRange.Rows.Count To 2 Step -1
If Cells(r, "D").Value = "INACTIVE" And Cells(r, "H").Value = "#N/A" Then
Sheet2.Rows(r).EntireRow.Delete
End If
Next
End With

You can use AutoFilter to get your rows and then delete them — Zac
– Zac, Commented Oct 31, 2019 at 10:05
Your If is checking the value of cells in the ActiveSheet, not in Sheet2. Maybe it should be something like If Sheet2.Cells(r, "D").Value or If ws3.Cells(r, "D").Value because you are mixing things here. ws3 is Sheet2 or is it Sheets("Sheet2")? is Sheet2=Sheets("Sheet2") your code is totally confusing. — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Oct 31, 2019 at 10:10

Ron Rosenfeld · Accepted Answer · 2019-10-31 10:51:32Z

2

Several issues.

You don't properly qualify your range objects.
- You (properly) use With ws3 but then never refer back to it
If the #N/A is an actual error value, and not a text string, your macro will fail with a type mismatch error.
If the first row of UsedRange is not row 1, then rows.count.row will not reflect the last row
r should be declared as Long, not Integer.
- Integer is limited to 32768 and there could be many more rows in a worksheet.
- VBA will convert Integer to Long internally anyway.
Also, as pointed out by @FoxfireAndBurnsAndBurns Sheets("Sheet2") may not be the same as Sheet2. You seem to be using them interchangeably in your code. Set ws3 to whichever one you really want. And examine vba HELP for CodeName to understand the difference.

The following modification of your code may work:

Option Explicit
Sub due()
  Dim ws3 As Worksheet
  Dim r As Long
  Dim lastRow As Long

Set ws3 = ThisWorkbook.Sheets("Sheet2")
With ws3
    lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
    For r = lastRow To 2 Step -1
        If .Cells(r, "D").Value = "INACTIVE" And .Cells(r, "H").Text = "#N/A" Then
            .Rows(r).EntireRow.Delete
        End If
    Next
End With

End Sub

edited Oct 31, 2019 at 10:51

answered Oct 31, 2019 at 10:43

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

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

3 Comments

shnaky Over a year ago

while using this code, I have got the erro as "invalid outside procedure" for option explicit

Ron Rosenfeld Over a year ago

@shnaky You have a typo or something other than what I have posted in your module.

Ron Rosenfeld Over a year ago

@shnaky Also note that the screen shot you showed in your duplicate question does not have ANY rows that contain INACTIVE

Collectives™ on Stack Overflow

Excel VBA - Delete Rows on certain conditions

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related