Macro to find text then delete all rows below

[kev8279]

Hi,

I have a spreadsheet which contains the words HM or NON HM in column A. I have wrote macro to sort data so HM comes first but what I want to do now is have a macro which finds the first instance of NON HM, then deletes that row and all subsequent rows below. Can anyone help?

 

[VoG]

Try

Sub NONHM()
Dim LR As Long, Found As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
Set Found = Columns("A").Find(what:="NON HM", LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then Rows(Found.Row & ":" & LR).Delete
End Sub

 

[JimM]

My very basic way (I'm learning !)

Sub Macro1()
Range("A1").Select

Cells.Find(What:="non HM", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
r = ActiveCell.Row

Rows(r & ":65536").EntireRow.Delete

End Sub

 

[THRASHER69]

Try

Sub NONHM()
Dim LR As Long, Found As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
Set Found = Columns("A").Find(what:="NON HM", LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then Rows(Found.Row & ":" & LR).Delete
End Sub

How would you write this to delete all rows above this that are 5 days older than Todays date?

 

[xsale]

Try

Sub NONHM()
Dim LR As Long, Found As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
Set Found = Columns("A").Find(what:="NON HM", LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then Rows(Found.Row & ":" & LR).Delete
End Sub

This is exactly the code I want but what could I change to make it delete all of the rows above "NON HM"? Also, can I change lookat:=xWhole to a different value since the word I am looking for is part of a title, not in a cell by itself.

Also sorry if I wasn't supposed to reuse an old thread but I tried reading thru the forum rules and couldn't find anything that said not to.