Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOPS IN MACROS
Hello
I'm trying to loop the below macro but having no luck. I want in excel to find a certain word e.g. NOTNEEDED and delete whole line then repeat until none left. Any help gratefully accepted. Range("A1").Select Dim FoundCell As Range With Worksheets("StockSheet") Range("A1").Select Set FoundCell = .Cells.Find(What:="NOTNEEDED", After:=ActiveCell, _ LookIn:=xlFormulas, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If FoundCell Is Nothing Then Else FoundCell.EntireRow.Delete End If End With |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOPS IN MACROS
Hi Heather
See http://www.rondebruin.nl/delete.htm If you want to use Find try http://www.rondebruin.nl/delete.htm#Find -- Regards Ron de Bruin http://www.rondebruin.nl "Heather O'Malley" wrote in message . .. Hello I'm trying to loop the below macro but having no luck. I want in excel to find a certain word e.g. NOTNEEDED and delete whole line then repeat until none left. Any help gratefully accepted. Range("A1").Select Dim FoundCell As Range With Worksheets("StockSheet") Range("A1").Select Set FoundCell = .Cells.Find(What:="NOTNEEDED", After:=ActiveCell, _ LookIn:=xlFormulas, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If FoundCell Is Nothing Then Else FoundCell.EntireRow.Delete End If End With |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOPS IN MACROS
This:
Sub cleanup() Dim r As Range Set r = ActiveSheet.UsedRange nLastRoW = r.Rows.Count + r.Row - 1 For i = nLastRoW To 1 Step -1 If Cells(i, "A").Value = "NOTNEEDED" Then Cells(i, "A").EntireRow.Delete End If Next End Sub will scan thru column A and remove rows with the match word. -- Gary''s Student "Heather O'Malley" wrote: Hello I'm trying to loop the below macro but having no luck. I want in excel to find a certain word e.g. NOTNEEDED and delete whole line then repeat until none left. Any help gratefully accepted. Range("A1").Select Dim FoundCell As Range With Worksheets("StockSheet") Range("A1").Select Set FoundCell = .Cells.Find(What:="NOTNEEDED", After:=ActiveCell, _ LookIn:=xlFormulas, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If FoundCell Is Nothing Then Else FoundCell.EntireRow.Delete End If End With |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
LOOPS IN MACROS
Using find look in the vba help for findNEXT. If in the same column you
could use datafilterautofiter and delete all at once.Record a macro and clean it up. -- Don Guillett SalesAid Software "Heather O'Malley" wrote in message . .. Hello I'm trying to loop the below macro but having no luck. I want in excel to find a certain word e.g. NOTNEEDED and delete whole line then repeat until none left. Any help gratefully accepted. Range("A1").Select Dim FoundCell As Range With Worksheets("StockSheet") Range("A1").Select Set FoundCell = .Cells.Find(What:="NOTNEEDED", After:=ActiveCell, _ LookIn:=xlFormulas, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If FoundCell Is Nothing Then Else FoundCell.EntireRow.Delete End If End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting phantom macros | Setting up and Configuration of Excel | |||
Hide Macro's in Toolbar / Macro's list | Excel Discussion (Misc queries) | |||
how do I run excel 4.0 macros on excel 2000 | Excel Discussion (Misc queries) | |||
Excel crashes while opening excel file imbeddied with macros | Excel Discussion (Misc queries) | |||
Transferring toolbars and macros to other computers | Excel Discussion (Misc queries) |