Ken,

I think you may have missed the caveat re 'OR below' <g

I'm unsure whether the poster wants to have the choice of above OR below but
it didn't sound like both.

--
__________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel


"Ken Wright" wrote in message
...
One way:-

Sub DelRows()

ans = InputBox("What string do you want to find and then delete all other
rows?")
Application.ScreenUpdating = False

LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count

Set Rng = Range(Cells(1, "A"), Cells(LastRow, "A"))

With Rng
.AutoFilter
.AutoFilter Field:=1, Criteria1:="<" & ans
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Application.ScreenUpdating = True

End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

--------------------------------------------------------------------------
--
It's easier to beg forgiveness than ask permission :-)
--------------------------------------------------------------------------
--



"Steven Rosenberg" wrote in message
...
How can I write a VBA macro which:

--will locate a specific value (a word) in column A of a
multi column worksheet; then

--select that cell's entire row, and all rows above (or
below) it; then

--delete the selected rows?

This non-programmer would appreciate any and all
help.

Steven

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.600 / Virus Database: 381 - Release Date: 28/02/2004