CODEWITHSUNDEEP
excelvba
Motornerve
Motornerve

Reputation: 25

Remove Rows based on values in 2 columns unless another column has a specific text character

So I have a sheet with 6 columns, and it has headers. In column F there are dates in mm - dd - yy format. In Column B there are numbers referencing the priority of the order, ranging from -999,999 to 473,700. In Column A there are Workcenter numbers in the form of numbers, 4005, 7009, etc. Some SPECIAL workcenters have names that end in R. I need to DELETE all rows that follow these criteria: - Greater than 0 in Column B - Assigned today's date in column F - DO NOT have an R at the end of the workcenter in column A

Here is what I have so far, but it is still deleting R workcenters (like 1027R)

I have looked at many different articles on this site, some using the AutoFilter tool, some using if value is this, then increment this var and add all vars together to check if the row should be deleted, but this is the cleanest I have gotten the code so far, and it is not working correctly. Also there are lots of articles about removing duplicates, but that is not my problem here.

Sub CleanData()
Dim lr As Long, i As Long
With Sheet3
    lr = Application.Max(.Cells(Rows.Count, 1).End(xlUp).Row, _
      .Cells(Rows.Count, 2).End(xlUp).Row, _
      .Cells(Rows.Count, 6).End(xlUp).Row)
    For i = lr To 1 Step -1
        If Not InStr(1, ActiveSheet.Cells(i, 2).Value, "R") > 0 And _
            .Cells(i, 2).Value > 0 And _
            .Cells(i, 6).Value = Date Then
            .Rows(i).EntireRow.Delete
        End If
    Next i
End With

End Sub

Upvotes: 1

Views: 40

Answers (1)

Glitch_Doctor
Glitch_Doctor

Reputation: 3034

Sub CleanData()
Dim lr As Long, i As Long
With Sheet3
    lr = Application.Max(.Cells(Rows.Count, 1).End(xlUp).Row, _
      .Cells(Rows.Count, 2).End(xlUp).Row, _
      .Cells(Rows.Count, 6).End(xlUp).Row)
    For i = lr To 1 Step -1
        If Not Application.Right(.Cells(i, 1).Value, 1) = "R" Then
            If .Cells(i, 2).Value > 0 And .Cells(i, 6).Value = Date Then
                .Rows(i).EntireRow.Delete
            End If
        End If
    Next i
End With

Your issue appears to have been If Not InStr(1, ActiveSheet.Cells(i, 2).Value, "R") where InStr is evaluating column B. I only changed to a nested if for my sanity as it reads easier... If you have any more issues I would think it may be with the format of Date.

Upvotes: 1

Related Questions