VBA Code to delete Rows

[howard]

I have a spreadsheet that wheneverv the following code appears in column A,
"WSP505 Company No: 10 Date: 10/06/200", 9 rows below and one row above it must be deleted.

The date 10/06/200 will change each month.

I have tried to write code with a wildcard character i.e. where "WSP505" occurs and anything after this code 9 rows below and one row above must be deleted.


See my code

nisswip.xls
	A	B	C	D	E	F
54	WSP505CompanyNo:10Date:10/06/200
Sheet1

I have attached sample data below

It would be appreciated if anyone could assist me

Thanks

Howard

nisswip.xls
	A	B	C	D	E	F
54	WSP505CompanyNo:10Date:10/06/200
Sheet1

 

[Jonmo1]

I asked that in my first post

So to be clear, we're looking for the first 6 characters in column A to be WSP505. And when found (in your example it's in row 54)...So
rows 55,56,57,58,59,60,61,62,63 And 53 Should be deleted, right ?

You said that was correct...

but anyway...

Replace these 2 lines

Range(i + 1 & ":" & i + 9).EntireRow.Delete
Rows(i - 1).Delete

With this 1 line

Range(i - 1 & ":" & i + 9).EntireRow.Delete

That should do it.

 

[howard]

Hi Jonmo1

Thanks for the help, much appreciated

Regards

Howard