Delete rows if cells has any value

[aayaanmayank]

Hi Can any one help me on this?

Based on ID match, if first cell and 2nd cell has any value then delete entire row. also if any of cell has value then it should not delete both the rows which contains same ID. i have written below code but it is not working properly.
MY data

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]ID[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<tbody>
[TD="class: xl65, width: 95"]CODE[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]419191389[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]DFGHJK[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]419191389[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]DFGHJK[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]13393262[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]LCPD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]13393262[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]16466396[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]LCPC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]16466396[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]LCPC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]24666862[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]24666862[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]26215556[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]LCPD[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]26215556[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]38694658[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]LCPC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]38694658[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95"]LCPC[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Sub may2()

Application.ScreenUpdating = False
Application.CutCopyMode = False
Set SHDQ = ThisWorkbook.Worksheets("U Assign DQ Team")


lastrow1 = SHDQ.Range("B" & Rows.Count).End(xlUp).Row


SHDQ.Activate


For O = 2 To lastrow1


    If SHDQ.Cells(O, "B").Value = SHDQ.Cells(O + 1, "B").Value Then
    If SHDQ.Cells(O, "R").Value = True And SHDQ.Cells(O + 1, "R").Value = True Then
       End If
       
       MsgBox ("DELET")
       End If
             
    Next




End Sub

 

[tonyyy]

aayaanmayank,

You might consider the following...

Sub may2()
Application.ScreenUpdating = False
Application.CutCopyMode = False

Set SHDQ = ThisWorkbook.Worksheets("U Assign DQ Team")
lastrow1 = SHDQ.Range("B" & Rows.Count).End(xlUp).Row
SHDQ.Activate

For O = lastrow1 To 2 Step -1
    If SHDQ.Cells(O, "B").Value = SHDQ.Cells(O - 1, "B").Value And _
        SHDQ.Cells(O, "R").Value = SHDQ.Cells(O - 1, "R").Value And SHDQ.Cells(O, "R").Value = "" Then
            MsgBox "Delete Row " & O & ", " & O - 1
    ElseIf SHDQ.Cells(O, "B").Value = SHDQ.Cells(O - 1, "B").Value And _
        SHDQ.Cells(O, "R").Value = SHDQ.Cells(O - 1, "R").Value Then
            MsgBox "Delete Row " & O
    ElseIf SHDQ.Cells(O, "B").Value = SHDQ.Cells(O - 1, "B").Value And _
        SHDQ.Cells(O, "R").Value = "" Then
            MsgBox "Delete Row " & O
    ElseIf SHDQ.Cells(O, "B").Value = SHDQ.Cells(O - 1, "B").Value And _
        SHDQ.Cells(O - 1, "R").Value = "" Then
            MsgBox "Delete Row " & O - 1
    End If
Next
End Sub

Cheers,

tonyyy