Thank for yor reply. I tried the chages but it does not work. So I
started over. I got the macro to find the value and select the the
entire row above. How can i select all row up to row 2.


Cells.Find(What:="Ground", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

ActiveCell.Offset(-1, 0).Select

Selection.EntireRow.Select


I need to select up to row 2



Thanks

On Mon, 16 Mar 2009 19:47:01 -0700, JLGWhiz
wrote:

Hi Little Penny, did you not want this:

Set myRange = ActiveSheet.Range("b" & rFound.Row _
& ":f" & myLastRow)

to be more like this?:

Set myRange = ActiveSheet.Range("b" & rFound.Row _
& ":f" & rFound.Row)


"Little Penny" wrote:

I want to find a value in colum "B" and delete all row above except
the first row.

I tried to adapt the code below without success.



Option Explicit

Sub delete_row_and_below()

Dim rFound As Range
Dim myRange As Range
Dim myLastRow As Long
Dim sName As String

myLastRow = ActiveSheet.Cells(65000, 2).End(xlUp).Row
Set myRange = ActiveSheet.Range("B1:B" & myLastRow)
sName = "LI-HANDTMG"

Set rFound = myRange.Find(What:=sName, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)

If rFound Is Nothing Then
MsgBox sName & " was not found in Range."
Else
Set myRange = ActiveSheet.Range("b" & rFound.Row _
& ":f" & myLastRow)
myRange.EntireRow.Delete
End If


End Sub



Thanks Little Penny