Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 Macro - Finding rows with data to copy | Excel Worksheet Functions | |||
Finding data between blank rows | Excel Worksheet Functions | |||
Finding common data in multiple columns and rows in Excel | Excel Worksheet Functions | |||
Select rows after finding last cell | Excel Programming | |||
Deleting rows A to E when finding dublicated Data in Column B | Excel Programming |