Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quick Search Loop
Hi All, I am trying to write a loop (which is part of a larger macro) that searches a worksheet and deletes the rows which contains the search text. Do Cells.Find(What:="Vendor Item", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate 'if activecell does contain "Vendor Item" then Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1, 0)).Delete Shift:=xlUp ActiveCell.Offset(1, 0).Activate 'end if Loop Until IsEmpty(ActiveCell) It does this successfully until there are no more of the search strings in the document, then it starts deleting any rows. (There is more data on the row which has vendor item). I realise i could put coding in, but I dont know how to proceed. Please help Thank you -- Sami82 ------------------------------------------------------------------------ Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111 View this thread: http://www.excelforum.com/showthread...hreadid=482114 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Quick Search Loop
Sub ABC() Dim rng As Range Set rng = Cells.Find(What:="Vendor Item", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not rng Is Nothing Then Do sAddr = rng.Address rng.Resize(2).Delete Shift:=xlUp Set rng = Range(sAddr) Set rng = Cells.FindNext(rng) Loop While Not rng Is Nothing End If End Sub -- Regards, Tom Ogilvy "Sami82" wrote in message ... Hi All, I am trying to write a loop (which is part of a larger macro) that searches a worksheet and deletes the rows which contains the search text. Do Cells.Find(What:="Vendor Item", After:=ActiveCell, LookIn:=xlFormulas _ , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate 'if activecell does contain "Vendor Item" then Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(1, 0)).Delete Shift:=xlUp ActiveCell.Offset(1, 0).Activate 'end if Loop Until IsEmpty(ActiveCell) It does this successfully until there are no more of the search strings in the document, then it starts deleting any rows. (There is more data on the row which has vendor item). I realise i could put coding in, but I dont know how to proceed. Please help Thank you -- Sami82 ------------------------------------------------------------------------ Sami82's Profile: http://www.excelforum.com/member.php...o&userid=27111 View this thread: http://www.excelforum.com/showthread...hreadid=482114 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Do Loop or use End iF for search string | Excel Discussion (Misc queries) | |||
Quick search required | Excel Discussion (Misc queries) | |||
suggest new users try quick search above 1st may help | Excel Discussion (Misc queries) | |||
how do i loop in a access search | Excel Programming | |||
search & copy with loop | Excel Programming |