Delete all rows after....

[Lande]

I am trying to delete all rows after a certain cell has a certain value. For example, in Column A, I have the following 10 rows:
1
2
3
4
Last Row
Not Needed
7
8
9
10

I am looking to delete all rows (or clear the contents) starting with the one that has the "Not Needed" in it, so I only want to keep rows 1 through "Last Row".

Thank you!

[JimmyTheHand]

Try this

[vba]Sub test()
Dim rng As Range
Set rng = Range("A:A").Find("Not Needed", , xlValues, xlPart, , , False)
If rng Is Nothing Then
MsgBox "String 'Not Needed' was not found."
Exit Sub
Else
Set rng = Range(rng, rng.End(xlDown))
rng.EntireRow.Delete
End If
End Sub
[/vba]

[Simon Lloyd]

Or try this:
[vba]
Sub Macro2()
Dim FndCell As String
Dim Rng As String
FndCell = Cells.Find(What:="Not Needed", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Address
Rng = Range("A65536").End(xlUp).Address
Range(Rng & ":" & FndCell).EntireRow.Delete (xlUp)

End Sub
[/vba]

[AlexAlexon]

Sorry, this is not my thread but can you explain Simon this part of your code "Range(Rng & ":" & FndCell)."

[Simon Lloyd]

Certainly!, this

Range(Rng & ":" & FndCell)

is the same as writing

Range("A1:B20")

where A1 would be the Rng i set in this statement

Rng = Range("A65536").End(xlUp).Address

i Declared In Memory the name Rng as a String because i used the suffix .Address at the end, FndCell is the name i had given to the results of the FIND function which is also a string, the : is used as in a normal range statement .

Hope this helps.