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