macro to Delete Rows where Ref is the Same and the branch in Col C contains statement

[howard]

I have references in Col B, Branch in Col C and Values in Col D

where the Reference is the Same in Col B and branch contains PL and statement and the statement Value is Zero , then then rows containing both the references are to be deleted


See link to sample data below , where I have indicated in red those rows to be deleted

It would be appreciated if someone would kindly provide me with code to do this

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Have also posted on macro to Delete Rows where Ref is the Same

 

[Osvaldo Palmeiro]

Hi. Try this, please.

Sub DeleteRows()
 Dim LR As Long
  Application.ScreenUpdating = False
  With ActiveSheet
   .AutoFilterMode = False
   .[E:E] = ""
   LR = .Cells(Rows.Count, 1).End(3).Row
   .Range("E2:E" & LR) = _
   "=IF(COUNTIFS(B$2:B$" & LR & ",""*""&""PL"",C$2:C$" & LR & ",C2)+COUNTIFS(C$2:C$" & LR & ",C2,D$2:D$" & LR & ",0)>1,""TBD"","""")"
   .Range("A1:E" & LR).AutoFilter 5, "TBD"
    If .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count > 1 Then
     .Range("A2:A" & LR).EntireRow.Delete
    End If
   .AutoFilterMode = False
   .[E:E] = ""
  End With
End Sub

 

[howard]

Thanks for the help

Where the Reference is duplicated in Col B and where the value for the second reference for branch containing "Statement" is zero, then those rows must be deleted

I have highlighted the rows to be deleted in yellow so you can get a better understanding of what I am trying to achieve

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Your assistance in modifying your code is appreciated

 

[Osvaldo Palmeiro]

See if I correctly understood what you meant. To work on the second sheet, which has a different lay out from the first one, just replace the formula to the code with this one below.

    "=IF(COUNTIFS(C$2:C$" & LR & ",""*""&""Statement"",B$2:B$" & LR & ",B2)+COUNTIFS(B$2:B$" & LR & ",B2,D$2:D$" & LR & ",0)>1,""TBD"","""")"

 

[howard]

Thanks Osvaldo


The amended code works perfectly