VBA Excel Help. Find a word, delete each row after it until it hits another word.

[stf27]

Hi. I'm new to VBA and I've been trying to figure out a way to find a word on a spreadsheet. Once found, I want to delete that row and every row after it until it hits a specific word. Once it hits that word the loop would stop.

Any ideas are appreciated. Thanks!

 

[Gary McMaster]

Maybe the following sample will serve as a starting point.

Hope it helps.

Gary

Public Sub Test()

Dim sWordStart As String
Dim sWordStop As String

Dim oStart As Range
Dim oStop As Range

Dim oDelete As Range

sWordStart = "Start word or phrase" ' Change text to suit
sWordStop = "End word or phrase" ' Change text to suit

Set oStart = ActiveSheet.UsedRange.Find(what:=sWordStart, lookat:=xlPart, MatchCase:=False)
Set oStop = ActiveSheet.UsedRange.Find(what:=sWordStop, lookat:=xlPart, MatchCase:=False)

If Not oStart Is Nothing And Not oStop Is Nothing Then
    Set oDelete = ActiveSheet.Range(oStart.Row & ":" & oStop.Offset(-1, 0).Row)
    oDelete.Interior.ColorIndex = 3
    'oDelete.Delete 'Uncomment to delete the target row(s)
End If

End Sub

 

[stf27]

Thanks for the help! There were a couple things I had to change when deleting the rows but other than that it worked wonderfully.

Thanks again!

 

[stf27]

Thanks for the help! There were a couple things I had to change when deleting the rows but other than that it worked wonderfully.

Thanks again!

[NOT RESOLVED!!]
One problem with this. I need to do the 'find then delete' in a loop. The problem is since I am not deleting Abilene, the .Find finds the same Abilene once it loops around. This completely screws up the data. How do I get the .Find to ignore the Abilene it just read?

 

[Gary McMaster]

Have a look at "FindNext" in VBA help. In XL2000 there is sample code that shows how to use Find / FindNext in a loop. Maybe the sample code also exists in higher versions. It involves saving / checking the cell address of the first Find to see if a subsequent Find has wrapped back around to the start cell.

Gary