Try the below and feedback. Create a named range called 'deletelist'..


Sub Macro()

Dim lngRow As Long, lngLastRow As Long
Dim lngTemp As Long, blnFound As Boolean

lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = 1 To lngLastRow
If blnFound = False Then
If WorksheetFunction.CountIf(Range("Deletelist"), Range("A" & lngRow)) 0
Then
blnFound = True: lngTemp = lngRow
End If
Else
If Trim(Range("A" & lngRow)) = "End" Then
Range("A" & lngTemp & ":A" & lngRow).ClearContents
blnFound = False
End If
End If
Next

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"bawpie" wrote:

Hi, I am looking to create a macro that can find data in a column then find
another specific entry in the same column and delete the data between the two
points. I have tried looking for other examples within the newsgroup but
most are to delete one specific row or to delete to the end of data etc and
therefore are not quite suitable.

Anyway, I have a worksheet. In Column A I might have:

A

Number 203
Service
Start Date
Closure
Reason for Closure
End
Number 205
Service
Start Date
Closure
Reason for Closure
End
Number 222
Service
Start Date
Closure
Reason for Closure
End

What I would like the macro to do is to locate 'Number 205' and delete each
row including Number 205 down to the first 'end' after 'Number 205.' The
rows between the 'Number' entry and the 'end' entry may sometimes vary so it
can't just locate the number and delete down a certain amount of rows.

Additionally, I would like the macro to work from a list located in another
sheet within the workbook, for example if I had a list like this in a sheet
called 'To Delete'
it would look like this:

A

Number 205
Number 222

So the macro needs to reference the list in the seperate sheet so it looks
for 205-end and deletes, then looks for 222-end and deletes and so on.

Hopefully there is a way of doing this, all advice and help is gratefully
received.

Thanks