About Us

George

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Excel 2003 Macro - Finding rows with data to copy	Debbie	Excel Worksheet Functions	1	March 23rd 10 01:23 PM
Finding data between blank rows	j.ruderman[_2_]	Excel Worksheet Functions	2	September 25th 08 11:48 AM
Finding common data in multiple columns and rows in Excel	sparham	Excel Worksheet Functions	3	February 12th 05 04:11 AM
Select rows after finding last cell	Sam Fowler[_2_]	Excel Programming	1	April 22nd 04 02:55 AM
Deleting rows A to E when finding dublicated Data in Column B	bkbri[_3_]	Excel Programming	6	December 15th 03 01:16 AM

Menu

About Us