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?
--
Teach me to fish! Thanks for the help.
Pax, M

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


"m stroup" wrote in message
...
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?
--
Teach me to fish! Thanks for the help.
Pax, M

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



On Nov 7, 11:35*am, m stroup
wrote:
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?
--
Teach me to fish! *Thanks for the help.
Pax, M

Thank you both for your quick response. I tweaked it a little and it is
working beautifully.
--
Teach me to fish! Thanks for the help.
Pax, M


"m stroup" wrote:

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?
--
Teach me to fish! Thanks for the help.
Pax, M