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]

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 ?

 

[howard]

Hi Jonmo1

Your assumptions are correct,except WSP505 appears as
WSP505


Howard

 

[Jonmo1]

except WSP505 appears as
WSP505

I don't understand what you mean here...what's the difference between
WSP505 and WSP505 ?

Anyway, give this a try.
SAVE YOUR FILE FIRST

[/COLOR]
Sub Test()
Dim LR, i
LR = Cells(Rows.Count, "A").End(xlUp).Row
For i = LR To 2 Step -1
    If left(Cells(i, "A"), 6) = "WSP505" Then
        Range(i + 1 & ":" & i + 9).EntireRow.Delete
        Rows(i - 1).Delete
        i = i - 1
    End If
Next i
End Sub

 

[Peter_SSs]

I don't understand what you mean here...what's the difference between
WSP505 and WSP505 ?

I think there is a space before the W, have a look at line 2 of the original post.

 

[Jonmo1]

I think there is a space before the W, have a look at line 2 of the original post.

Ahh OK, then if that's the case, change this line

If left(Cells(i, "A"), 6) = "WSP505" Then

to

If left(Cells(i, "A"), 7) = " WSP505" Then

 

[Peter_SSs]

howard

Approximately how many rows of data do you have?

 

[Norie]

I don't actually think it is a space, looks more like some sort of non-printing character.

Perhaps using InStr would be more appropriate?

 

[Jonmo1]

Peter also makes a good point about the amount of data, probably a good idea to turn off screenupdating, events and calculation..

And the Instr would probably be better...

Sub Test()
Dim LR, i
 
With Application.
    .ScreenUpdating = False
    .Calculation = XlCalculationManual
    .EnableEvents = False
End With
 
LR = Cells(Rows.Count, "A").End(xlUp).Row
For i = LR To 2 Step -1
    If Instr(1,Cells(i, "A").Value,"WSP505") > 0 Then
        Range(i + 1 & ":" & i + 9).EntireRow.Delete
        Rows(i - 1).Delete
        i = i - 1
    End If
Next i
 
With Application.
    .ScreenUpdating = True
    .Calculation = XlCalculationAutomatic
    .EnableEvents = True
End With
 
End Sub

 

[howard]

Hi Jonmo1

Thanks for the help. The only line that is not delete is the line containing the information

WSP505 Company No: 10 Date: 10/06/200

It would be appreciated if you would amend your code to delete this row as well

Howard