CODEWITHSUNDEEP
excelvba
shnaky
shnaky

Reputation: 11

Excel VBA - Delete Rows on certain conditions

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

Upvotes: 1

Views: 182

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60464

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

Upvotes: 2

Related Questions