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".

 

[akramer08]

(3 P's and D... delete them all; Columns A, B, C, D all the same, delete all but one)?
Rick,
I just read over what you last posted. That would work perfectly. Only problem is that the value in col D will not be a "D", it will be a number. Sometimes that number will be duplicated. So it is basically what you said above in red, except instead of a D it will be need to be a duplicate number.

 

[Rick Rothstein]

(3 P's and D... delete them all; Columns A, B, C, D all the same, delete all but one)?
Rick,
I just read over what you last posted. That would work perfectly. Only problem is that the value in col D will not be a "D", it will be a number. Sometimes that number will be duplicated. So it is basically what you said above in red, except instead of a D it will be need to be a duplicate number.

I think this does what you want (obviously, test it)...

Sub DeleteDupes()
  Dim LastRow As Long, UnusedColumn 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(SUMPRODUCT(--(RC1&RC2&RC3&RC4=R1C1:RC1&R1C2:RC2&R1C3:RC3&R1C4:RC4))>1,""X"",IF(SUMPRODUCT(--(RC1&RC2&RC3&RC4=R1C1:RC1&R1C2:RC2&R1C3:RC3&R1C4:RC4))>1,""X"",""""))"
    .Value = .Value
    On Error Resume Next
    .SpecialCells(xlConstants).EntireRow.Delete
  End With
End Sub

 

[akramer08]

Not bad sir. Just using that code only deleted the dupes but it left behind all the ones with the 3 P's. I just used the first code you gave me earlier and after it deletes all the records with 3 P's, it calls the one you just posted. I did a small test sample and it worked just fine. Thanks for all the help and my apologies about being so vague.

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
Run "DeleteDupes"
End Sub
Sub DeleteDupes()
  Dim LastRow As Long, UnusedColumn 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(SUMPRODUCT(--(RC1&RC2&RC3&RC4=R1C1:RC1&R1C2:RC2&R1C3:RC3&R1C4:RC4))>1,""X"",IF(SUMPRODUCT(--(RC1&RC2&RC3&RC4=R1C1:RC1&R1C2:RC2&R1C3:RC3&R1C4:RC4))>1,""X"",""""))"
    .Value = .Value
    On Error Resume Next
    .SpecialCells(xlConstants).EntireRow.Delete
  End With
End Sub

 

[akramer08]

One thing. Instead of the Columns being A, B, C, and D, I need Col AC, AD, AE, and AJ. How can I change that?