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
|