Find and Delete

[Little Penny]

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

 

[JLGWhiz]

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)

 

[JLGWhiz]

Sorry, you said all above except first row. I am assuming you also want to
delete the found row. If not, just change to rFound - 1.

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

If you want to include column A in the delete, change b2 to A2.

To delete the entire row:

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

 

[Little Penny]

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

 

[JLGWhiz]

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]

That worked

Thanks

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.