That worked

Thanks






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

There was nothing wrong with the first procedure that you posted, ecept the
line for the variable myRange. After reading your post again, it looks like
what you want to use is:

Set myRange = ActiveSheet.Range("b2:b" & rFound.Row)
myRange.EntireRow.Delete

This will delete rows 2 through the found row.


"Little Penny" wrote:

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