Find row and delete following rows

[m stroup]

I want to search for a value in a cell and delete the row that contains it
and all others below it. I am struggling with how to define that as a range.
Suggestions?

 

[Bernie Deitrick]

Sub TryNow()
Dim myC As Range
Set myC = Cells.Find("What you are looking for")
If myC Is Nothing Then
MsgBox "That wasn't found"
Else
Range(myC, myC.End(xlDown)).EntireRow.Delete
End If
End Sub

If you are looking for a number, don't use the quotes.

HTH,
Bernie
MS Excel MVP

 

[Susan]

this is what i came up with - i assumed your value was a string - you
could change that.........
'===================
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(10000, 1).End(xlUp).Row
Set myRange = ActiveSheet.Range("a1:a" & myLastRow)
sName = "Paul"

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("a" & rFound.Row _
& ":f" & myLastRow)
myRange.EntireRow.Delete
End If

End Sub
'========================
hope it gives you a place to start!

susan

 

[m stroup]

Thank you both for your quick response. I tweaked it a little and it is
working beautifully.