Macro to delete rows in excel to cell with specific key word in excel and next find if cell in row is blank then delete row with blank cell and above

[Kuldip]

Frnds,


I need a Macro to delete rows in excel to cell with specific keyword "Job Number" in excel and next find if cell exactly next row is blank, if yes then delete this row with blank cell and above two.

Report Data copy
[TABLE="width: 693"]
<tbody>[TR]
[TD]Data 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job No[/TD]
[TD]Date[/TD]
[TD]Client[/TD]
[TD]Account[/TD]
[TD]Group Account[/TD]
[TD]Location[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]XXX1234[/TD]
[TD]17 May 16[/TD]
[TD]ABC[/TD]
[TD]CDF[/TD]
[TD]EFG[/TD]
[TD]USA[/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD]XXX1235[/TD]
[TD]18 May 16[/TD]
[TD]GDC[/TD]
[TD]ADF[/TD]
[TD]EFG[/TD]
[TD]USA[/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job No[/TD]
[TD]Date[/TD]
[TD]Client[/TD]
[TD]Account[/TD]
[TD]Group Account[/TD]
[TD]Location[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job No[/TD]
[TD]Date[/TD]
[TD]Client[/TD]
[TD]Account[/TD]
[TD]Group Account[/TD]
[TD]Location[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]XXX1234[/TD]
[TD]17 May 16[/TD]
[TD]ABC[/TD]
[TD]CDF[/TD]
[TD]EFG[/TD]
[TD]BRA[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]XXX1235[/TD]
[TD]18 May 16[/TD]
[TD]GDC[/TD]
[TD]ADF[/TD]
[TD]EFG[/TD]
[TD]USA[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]XXX1236[/TD]
[TD]19 May 16[/TD]
[TD]ABC[/TD]
[TD]CDF[/TD]
[TD]EFG[/TD]
[TD]EGY[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]XXX1237[/TD]
[TD]20 May 16[/TD]
[TD]GDC[/TD]
[TD]ADF[/TD]
[TD]EFG[/TD]
[TD]SRT[/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job No[/TD]
[TD]Date[/TD]
[TD]Client[/TD]
[TD]Account[/TD]
[TD]Group Account[/TD]
[TD]Location[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job No[/TD]
[TD]Date[/TD]
[TD]Client[/TD]
[TD]Account[/TD]
[TD]Group Account[/TD]
[TD]Location[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]XXX1236[/TD]
[TD]19 May 16[/TD]
[TD]ABC[/TD]
[TD]CDF[/TD]
[TD]EFG[/TD]
[TD]EGY[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Expected Results - Deleting blank records and respective headers.

[TABLE="width: 576"]
<tbody>[TR]
[TD]Data 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job No[/TD]
[TD]Date[/TD]
[TD]Client[/TD]
[TD]Account[/TD]
[TD]Group Account[/TD]
[TD]Location[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]XXX1234[/TD]
[TD="align: right"]17 May 16[/TD]
[TD]ABC[/TD]
[TD]CDF[/TD]
[TD]EFG[/TD]
[TD]USA[/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD]XXX1235[/TD]
[TD="align: right"]18 May 16[/TD]
[TD]GDC[/TD]
[TD]ADF[/TD]
[TD]EFG[/TD]
[TD]USA[/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job No[/TD]
[TD]Date[/TD]
[TD]Client[/TD]
[TD]Account[/TD]
[TD]Group Account[/TD]
[TD]Location[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]XXX1234[/TD]
[TD="align: right"]17 May 16[/TD]
[TD]ABC[/TD]
[TD]CDF[/TD]
[TD]EFG[/TD]
[TD]BRA[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]XXX1235[/TD]
[TD="align: right"]18 May 16[/TD]
[TD]GDC[/TD]
[TD]ADF[/TD]
[TD]EFG[/TD]
[TD]USA[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]XXX1236[/TD]
[TD="align: right"]19 May 16[/TD]
[TD]ABC[/TD]
[TD]CDF[/TD]
[TD]EFG[/TD]
[TD]EGY[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]XXX1237[/TD]
[TD="align: right"]20 May 16[/TD]
[TD]GDC[/TD]
[TD]ADF[/TD]
[TD]EFG[/TD]
[TD]SRT[/TD]
[TD]Pending[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job No[/TD]
[TD]Date[/TD]
[TD]Client[/TD]
[TD]Account[/TD]
[TD]Group Account[/TD]
[TD]Location[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD]XXX1236[/TD]
[TD="align: right"]19 May 16[/TD]
[TD]ABC[/TD]
[TD]CDF[/TD]
[TD]EFG[/TD]
[TD]EGY[/TD]
[TD]Completed[/TD]
[/TR]
</tbody>[/TABLE]

NB, If its a blank records then it will only be single blank row below "Job No" as below, which needs to be deleted in results

[TABLE="width: 693"]
<tbody>[TR]
[TD]Data 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Job No[/TD]
[TD]Date[/TD]
[TD]Client[/TD]
[TD]Account[/TD]
[TD]Group Account[/TD]
[TD]Location[/TD]
[TD]Status[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks in advance for your help.

Rgrds
Kuldip

 

[Kuldip]

Sub DeleteUnwantedRows()


Application.ScreenUpdating = False
Dim x
For x = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row To ActiveCell.Row Step -1
If Cells(x, 1) = "Job No" And Cells(x + 1, 1) = "" Then
Cells(x, 1).EntireRow.Delete 'Delete the entire row
Cells(x - 1, 1).EntireRow.Delete 'Delete the row above it
Cells(x - 2, 1).EntireRow.Delete 'Delete the row 2 rows above it


End If
Next x
Application.ScreenUpdating = True


End Sub

Thanks guys i got it though not perfect but results are somewhat i wanted.