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

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

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

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

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