Hi Chip,
I am having another go at this.
I have modified your code slightly:

Sub AAA()
Const WHAT_COLUMN = "k"
Dim LastCell As Range
Dim FoundCell As Range

Set FoundCell = ActiveSheet.UsedRange.Find("k")
If FoundCell Is Nothing Then
' not found. get out.
Exit Sub
End If
With ActiveSheet
Set LastCell = .Cells(.Rows.count, WHAT_COLUMN).End(xlUp)
.Range(FoundCell(2000, 1), LastCell).EntireRow.Delete
End With
End Sub

It works appart from it deletes the last row of data.
Is there some sort of an offset (Have I got that right) so that it wont
delete the last so many rows below the last value (10 rows for example)?

Many thanks

John





"Chip Pearson" wrote:

Try something like the following:

Sub AAA()
Const WHAT_COLUMN = "A"
Dim LastCell As Range
Dim FoundCell As Range

Set FoundCell = ActiveSheet.UsedRange.Find("abc")
If FoundCell Is Nothing Then
' not found. get out.
Exit Sub
End If
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, WHAT_COLUMN).End(xlUp)
.Range(FoundCell(11, 1), LastCell).EntireRow.Delete
End With
End Sub

Change WHAT_COLUMN to the column in which the specified value ("abc"
in this example) is to be found.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Wed, 14 Jan 2009 10:15:01 -0800, JohnUK
wrote:

Hi, Can anyone help with this. I need a piece of code that can delete all
rows in one swoop, working from the last/end row to the 10th row below a
given column with values in it. I hope I explained that okay. Help greatly
appreciated
John