Macro to delete Rows

[howard]

I have tried to wrire code to delete rows starting from one row above where txt " Detail L" appears to 9 rows below where "Detail L" , but cannot get the code to work properly

I have attached sample data. The rows to be deleted must be 56 to 65 in this example-see my code and sample data below

Your assistance will be most appreciated

Sub Del_Unwanted()
Finalrow = Cells(65536, 1).End(xlUp).Row
For i = Finalrow To 5 Step -1
If Cells(i, 1).Value = "Detail L" Then

Cells(i, 1).Offset(-1).Resize(10).EntireRow.Delete
End If
Next i
end Sub

East1101P.xls
	A	B	C	D	E	F	G
55	684	PTS26/02/11	FrontCounterInvoice	84103	57	57
56
57	DetailL	ineAgeing:N	00Current-37939.93P1	7939.93
58	EA			MonFe	b2810:27:48	2011Pa
59	========	=============	=======================	========	==============	===========
60
61	Nominal	account
62	--------	-------------
63
64	RefNo	JrnDate	Narrative	Ctl	Transamt.	Ctlbalance
65	------	-------	---------	---	----------	----------
66	685	PTS26/02/11	FrontCounterInvoice	84104	508.95	508.95
Sheet1

 

[VoG]

Try

Sub Del_Unwanted()
Dim Found As Range
Set Found = Columns("A").Find(what:="Detail L", LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then Found.Resize(10).EntireRow.Delete
End Sub

 

[snb_]

or

Sub Del_Unwanted()
  on error resume next
  Columns(1).Find("Detail L", ,xlValues, xlWhole).Resize(10).EntireRow.Delete
End Sub

 

[howard]

Hi Guys

Thanks for the help, much appreciated

Regards

Howard

 

[howard]

Try

Sub Del_Unwanted()
Dim Found As Range
Set Found = Columns("A").Find(what:="Detail L", LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then Found.Resize(10).EntireRow.Delete
End Sub

Hi Peter

You helped me the other day with code to delete one row above where the word "detail" appeared and 10 rows below. This code deleted the coorect amount of rows where detail appears once, but when there are several page breaks where the word "detail" appears, the code does not delete these. It would be appreciated if you would amend your code to take this into account

Thanks

Howard

 

[VoG]

Hi. Try this

Sub Del_Unwanted()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Range("A" & i).Value = "Detail L" Then Range("A" & i).Resize(10).EntireRow.Delete
Next i
End Sub

 

[howard]

Hi Peter

Thanks for the help, much appreciated

 

[howard]

Hi. Try this

Sub Del_Unwanted()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Range("A" & i).Value = "Detail L" Then Range("A" & i).Resize(10).EntireRow.Delete
Next i
End Sub

Hi Peter

When activating the macro, the code
"If Range("A" & i).Value = "Detail L" Then Range("A" & i).Resize(10).EntireRow.Delete" is highlighted

It would be appreciated if you would test and correct

Regards

Howard

 

[VoG]

Hi. Perhaps you have error values in Column A. Try

Sub Del_Unwanted()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If Not IsError(Range("A" & i)) Then
        If Range("A" & i).Value = "Detail L" Then Range("A" & i).Resize(10).EntireRow.Delete
    End If
Next i
End Sub

 

[howard]

Hi Peter

Thanks for the help. The macro runs perfectly

Have a great weekend