delete from : to n rows after finding data in a cell

I want to find some text in col A and delete all rows until i find test in
col D.
e.g
find "Bank account name" in col A.
Then delete all rows down to "closing balance" in col d (do not delete Col d)
Any suggestions? cheers

Do not delete column D--does that mean don't delete the row with closing balance
on it?

This seems to work ok for me:

Option Explicit
Sub testme()
Dim FoundCellTop As Range
Dim FoundCellBot As Range
Dim WhatToFindTop As String
Dim WhatToFindBot As String

WhatToFindTop = "Bank Account Name"
WhatToFindBot = "Closing Balance"

With Worksheets("sheet1")
With .Range("a:a")
Set FoundCellTop = .Cells.Find(what:=WhatToFindTop, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

With .Range("d:d")
Set FoundCellBot = .Cells.Find(what:=WhatToFindBot, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With


If FoundCellTop Is Nothing _
Or FoundCellBot Is Nothing Then
MsgBox "At least one wasn't found!"
Exit Sub
End If

If (FoundCellBot.Row - FoundCellTop.Row) < 2 Then
MsgBox "nothing to delete"
Else
.Range(FoundCellTop.Offset(1, 0), _
FoundCellBot.Offset(-1, 0)).EntireRow.Delete
End If
End With

End Sub



George wrote:
>
> I want to find some text in col A and delete all rows until i find test in
> col D.
> e.g
> find "Bank account name" in col A.
> Then delete all rows down to "closing balance" in col d (do not delete Col d)
> Any suggestions? cheers

--

Dave Peterson

Genius!
Thanks Dave

"Dave Peterson" wrote:

> Do not delete column D--does that mean don't delete the row with closing balance
> on it?
>
> This seems to work ok for me:
>
> Option Explicit
> Sub testme()
> Dim FoundCellTop As Range
> Dim FoundCellBot As Range
> Dim WhatToFindTop As String
> Dim WhatToFindBot As String
>
> WhatToFindTop = "Bank Account Name"
> WhatToFindBot = "Closing Balance"
>
> With Worksheets("sheet1")
> With .Range("a:a")
> Set FoundCellTop = .Cells.Find(what:=WhatToFindTop, _
> after:=.Cells(.Cells.Count), _
> LookIn:=xlValues, _
> lookat:=xlWhole, _
> searchorder:=xlByRows, _
> searchdirection:=xlNext, _
> MatchCase:=False)
> End With
>
> With .Range("d:d")
> Set FoundCellBot = .Cells.Find(what:=WhatToFindBot, _
> after:=.Cells(.Cells.Count), _
> LookIn:=xlValues, _
> lookat:=xlWhole, _
> searchorder:=xlByRows, _
> searchdirection:=xlNext, _
> MatchCase:=False)
> End With
>
>
> If FoundCellTop Is Nothing _
> Or FoundCellBot Is Nothing Then
> MsgBox "At least one wasn't found!"
> Exit Sub
> End If
>
> If (FoundCellBot.Row - FoundCellTop.Row) < 2 Then
> MsgBox "nothing to delete"
> Else
> .Range(FoundCellTop.Offset(1, 0), _
> FoundCellBot.Offset(-1, 0)).EntireRow.Delete
> End If
> End With
>
> End Sub
>
>
>
> George wrote:
> >
> > I want to find some text in col A and delete all rows until i find test in
> > col D.
> > e.g
> > find "Bank account name" in col A.
> > Then delete all rows down to "closing balance" in col d (do not delete Col d)
> > Any suggestions? cheers
>
> --
>
> Dave Peterson
>