VBA delete entire row if col A,B,C in that row display a "P"

[akramer08]

I am looking for a macro that will delete an entire row if the values in that row in column A,B,C each equal "P".

 

[Rick Rothstein]

Give this macro a try...

Sub PPPdeleter()
  Dim UnusedColumn As Long, LastRow As Long
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlValues).Row
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                 SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  With Cells(1, UnusedColumn).Resize(LastRow)
    .FormulaR1C1 = "=IF(RC1&RC2&RC3=""PPP"",""X"","""")"
    .Value = .Value
    On Error Resume Next
    .SpecialCells(xlConstants).EntireRow.Delete
    On Error GoTo 0
  End With
End Sub

 

[akramer08]

Works great. One other thing. This is to delete duplicates off of a report. Some duplicates have the three P's, some dont. I only need to delete the duplicates that have the 3 P's. Is there a way to filter a list by duplicates then use visible cells to delete the duplicates with 3 P's, or can it be built into the macro that it only deletes a row if the value in col D is a duplicate and it shows the 3 P's?

 

[Rick Rothstein]

Works great. One other thing. This is to delete duplicates off of a report. Some duplicates have the three P's, some dont. I only need to delete the duplicates that have the 3 P's. Is there a way to filter a list by duplicates then use visible cells to delete the duplicates with 3 P's, or can it be built into the macro that it only deletes a row if the value in col D is a duplicate and it shows the 3 P's?

Okay, so this is a new condition from what you asked for originally... only delete the rows with Columns A,B and C equal to P and where Column D of that row has a D, right? If so, give this code a try...

Sub PPPdeleter()
  Dim UnusedColumn As Long, LastRow As Long
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _
            SearchDirection:=xlPrevious, LookIn:=xlValues).Row
  UnusedColumn = Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                 SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
  With Cells(1, UnusedColumn).Resize(LastRow)
    .FormulaR1C1 = "=IF(RC1&RC2&RC3&RC4=""PPPD"",""X"","""")"
    .Value = .Value
    On Error Resume Next
    .SpecialCells(xlConstants).EntireRow.Delete
    On Error GoTo 0
  End With
End Sub

 

[akramer08]

I apologize, I dont think I explained it very clearly. I need it to delete the entire row if the value in column D is a duplicate of another records value in column D and if columns A,B,and C are equal to "P". Thats why I didnt know if it would be easier to build that part in the macro or if there is a way to filter column D by duplicates first, then use special cells.

 

[Rick Rothstein]

I apologize, I dont think I explained it very clearly. I need it to delete the entire row if the value in column D is a duplicate of another records value in column D and if columns A,B,and C are equal to "P". Thats why I didnt know if it would be easier to build that part in the macro or if there is a way to filter column D by duplicates first, then use special cells.

Question: If you have two rows whose D value is the same and which both have P's in Columns A, B and C... do both rows get deleted? I ask because once the first one is deleted, there is no duplicate any more for that second one. Also, if the answer is to only delete one of the two duplicates, which one should be deleted... the one with the lowest or highest row number? What about if there are three such duplicated rows... which one remains?

 

[akramer08]

If there are duplicate records and they all have the 3 P's, they can all be deleted. There is a slightly different thing that I could greatly use. Sometimes the values in Col A,B,C are not P's. Sometimes the value are Y's, N's, A's. My question is can we delete an entire row based on if the value in Col D is a duplicate AND if all three values in A, B, and C are duplicates as well.

For example if I have two records that display as below, I would only need one of them to show on the report.
Col A...Col B...Col C......Col D
Y...N...P......987
Y...N...P......987<-------either this or the one above it would be deleted
Y...N...A......987<-------this one would not be deleted as it has a different value in col C than the other two records.

 

[tusharm]

Maybe, your requirements make sense to Rick (or to someone else) but to me it appears you are changing them not only from 1 post to the next but even within the same post. {grin}

In the below you start by stating that "they can all be deleted." But, then, in the example you indicate "either this or the one above would be deleted."

Which is it? All but one? All? Something else?

It often helps to (1) think through the *whole* problem up front and (2) share the *whole* problem. This piecemeal approach may make sense to you but it can cause the *volunteers* who help you to essentially view their previous assistance as a waste. After all, they (or in this case he) spend time and energy addressing an issue that isn't even your real problem!

If there are duplicate records and they all have the 3 P's, they can all be deleted. There is a slightly different thing that I could greatly use. Sometimes the values in Col A,B,C are not P's. Sometimes the value are Y's, N's, A's. My question is can we delete an entire row based on if the value in Col D is a duplicate AND if all three values in A, B, and C are duplicates as well.

For example if I have two records that display as below, I would only need one of them to show on the report.
Col A...Col B...Col C......Col D
Y...N...P......987
Y...N...P......987<-------either this or the one above it would be deleted
Y...N...A......987<-------this one would not be deleted as it has a different value in col C than the other two records.

 

[Rick Rothstein]

If there are duplicate records and they all have the 3 P's, they can all be deleted. There is a slightly different thing that I could greatly use. Sometimes the values in Col A,B,C are not P's. Sometimes the value are Y's, N's, A's. My question is can we delete an entire row based on if the value in Col D is a duplicate AND if all three values in A, B, and C are duplicates as well.

For example if I have two records that display as below, I would only need one of them to show on the report.
Col A...Col B...Col C......Col D
Y...N...P......987
Y...N...P......987<-------either this or the one above it would be deleted
Y...N...A......987<-------this one would not be deleted as it has a different value in col C than the other two records.

The two statements in red contradict each other. Or are you saying the three P's in Columns A, B, C plus the D in ColumnD test is completely independent from the Columns A, B, C and D duplicates and are treated differently (3 P's and D... delete them all; Columns A, B, C, D all the same, delete all but one)?

 

[akramer08]

Col D will always be a number, it wont contain a D.

I no longer need to remove records that have the 3 P's. Instead, if those 3 values and the number in Col D are duplicates of another record, all but one of those records need to be deleted.