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

Data 1
Job No	Date	Client	Account	Group Account	Location	Status
XXX1234	17 May 16	ABC	CDF	EFG	USA	Pending
XXX1235	18 May 16	GDC	ADF	EFG	USA	Pending
Data 2
Job No	Date	Client	Account	Group Account	Location	Status
Data 3
Job No	Date	Client	Account	Group Account	Location	Status
XXX1234	17 May 16	ABC	CDF	EFG	BRA	Completed
XXX1235	18 May 16	GDC	ADF	EFG	USA	Completed
XXX1236	19 May 16	ABC	CDF	EFG	EGY	Completed
XXX1237	20 May 16	GDC	ADF	EFG	SRT	Pending
Data 4
Job No	Date	Client	Account	Group Account	Location	Status
Data 5
Job No	Date	Client	Account	Group Account	Location	Status
XXX1236	19 May 16	ABC	CDF	EFG	EGY	Completed

<tbody>
</tbody>

Expected Results - Deleting blank records and respective headers.

Data 1
Job No	Date	Client	Account	Group Account	Location	Status
XXX1234	17 May 16	ABC	CDF	EFG	USA	Pending
XXX1235	18 May 16	GDC	ADF	EFG	USA	Pending
Data 3
Job No	Date	Client	Account	Group Account	Location	Status
XXX1234	17 May 16	ABC	CDF	EFG	BRA	Completed
XXX1235	18 May 16	GDC	ADF	EFG	USA	Completed
XXX1236	19 May 16	ABC	CDF	EFG	EGY	Completed
XXX1237	20 May 16	GDC	ADF	EFG	SRT	Pending
Data 5
Job No	Date	Client	Account	Group Account	Location	Status
XXX1236	19 May 16	ABC	CDF	EFG	EGY	Completed

<tbody>
</tbody>

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

Data 4
Job No	Date	Client	Account	Group Account	Location	Status

<tbody>
</tbody>

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.