Take out row...
Ok AMIGOS...
Here is a macro that goes to column "F" finds the the cell that contains "take out" then deletes the row. The problem is that it takes to long to run. So here is what I want to do. I sorted the "F" column to group all the "take out" cells together, so the macro will start deleting the all rows first then will reach a point when it will no longer find "Take out" cells and stop looking. So now the macro will not have to search the hole list. Is this Possible... Keep In Touch Marco Estrella Range("F:F").Select With Selection Set C = .Find("Take Out", LookIn:=xlValues) If Not C Is Nothing Then firstAddress = C.Address Do Set d = C.Offset(-1, 0) C.EntireRow.Delete Set C = .FindNext(d) Loop While Not C Is Nothing End If End With I am not so sure but I tink I found this macro in http://www.cpearson.com/excel.htm Cool stuff there. |
Take out row...
Select column F, do Data=Filter=Autofilter
Filter on Take Out in the drop down in Column F Select all the rows Do edit =Delete Do Data=Filter=Autofilter to remove the filter Turn on the macro recorder while you do the actions manually to get the code to mimic these actions. Adjust the recorded code to make it more general. -- Regards, Tom Ogilvy "MESTRELLA29" wrote in message ... Ok AMIGOS... Here is a macro that goes to column "F" finds the the cell that contains "take out" then deletes the row. The problem is that it takes to long to run. So here is what I want to do. I sorted the "F" column to group all the "take out" cells together, so the macro will start deleting the all rows first then will reach a point when it will no longer find "Take out" cells and stop looking. So now the macro will not have to search the hole list. Is this Possible... Keep In Touch Marco Estrella Range("F:F").Select With Selection Set C = .Find("Take Out", LookIn:=xlValues) If Not C Is Nothing Then firstAddress = C.Address Do Set d = C.Offset(-1, 0) C.EntireRow.Delete Set C = .FindNext(d) Loop While Not C Is Nothing End If End With I am not so sure but I tink I found this macro in http://www.cpearson.com/excel.htm Cool stuff there. |
Take out row...
OK Tom this was a good Idea but, somthing is not right, wile i am recording
the macro and reach Delete row, my excel goes to "Not Responding" and dies... you know why? "Tom Ogilvy" wrote: Select column F, do Data=Filter=Autofilter Filter on Take Out in the drop down in Column F Select all the rows Do edit =Delete Do Data=Filter=Autofilter to remove the filter Turn on the macro recorder while you do the actions manually to get the code to mimic these actions. Adjust the recorded code to make it more general. -- Regards, Tom Ogilvy "MESTRELLA29" wrote in message ... Ok AMIGOS... Here is a macro that goes to column "F" finds the the cell that contains "take out" then deletes the row. The problem is that it takes to long to run. So here is what I want to do. I sorted the "F" column to group all the "take out" cells together, so the macro will start deleting the all rows first then will reach a point when it will no longer find "Take out" cells and stop looking. So now the macro will not have to search the hole list. Is this Possible... Keep In Touch Marco Estrella Range("F:F").Select With Selection Set C = .Find("Take Out", LookIn:=xlValues) If Not C Is Nothing Then firstAddress = C.Address Do Set d = C.Offset(-1, 0) C.EntireRow.Delete Set C = .FindNext(d) Loop While Not C Is Nothing End If End With I am not so sure but I tink I found this macro in http://www.cpearson.com/excel.htm Cool stuff there. |
Take out row...
I don't know, but possibly something like this:
Sub ProcTakeOut() Dim rng As Range Dim rng1 As Range Dim rng2 As Range With ActiveSheet If .AutoFilterMode Then .AutoFilterMode = False End If Set rng = Intersect(.Columns(6), .UsedRange).Cells End With rng.AutoFilter Field:=1, Criteria1:="Take Out" Set rng1 = rng.Offset(1, 0).Resize(rng.Rows.Count - 1) On Error Resume Next Set rng2 = rng1.SpecialCells(xlVisible) On Error GoTo 0 If Not rng2 Is Nothing Then rng2.EntireRow.Delete End If rng.AutoFilter End Sub -- Regards, Tom Ogilvy "MESTRELLA29" wrote in message ... OK Tom this was a good Idea but, somthing is not right, wile i am recording the macro and reach Delete row, my excel goes to "Not Responding" and dies... you know why? "Tom Ogilvy" wrote: Select column F, do Data=Filter=Autofilter Filter on Take Out in the drop down in Column F Select all the rows Do edit =Delete Do Data=Filter=Autofilter to remove the filter Turn on the macro recorder while you do the actions manually to get the code to mimic these actions. Adjust the recorded code to make it more general. -- Regards, Tom Ogilvy "MESTRELLA29" wrote in message ... Ok AMIGOS... Here is a macro that goes to column "F" finds the the cell that contains "take out" then deletes the row. The problem is that it takes to long to run. So here is what I want to do. I sorted the "F" column to group all the "take out" cells together, so the macro will start deleting the all rows first then will reach a point when it will no longer find "Take out" cells and stop looking. So now the macro will not have to search the hole list. Is this Possible... Keep In Touch Marco Estrella Range("F:F").Select With Selection Set C = .Find("Take Out", LookIn:=xlValues) If Not C Is Nothing Then firstAddress = C.Address Do Set d = C.Offset(-1, 0) C.EntireRow.Delete Set C = .FindNext(d) Loop While Not C Is Nothing End If End With I am not so sure but I tink I found this macro in http://www.cpearson.com/excel.htm Cool stuff there. |
Take out row...
Thanks Tom,,, that did the job,
You have reduce the Time of the Macro From 4.27 m to 2.10 m, for now that is Great... "Tom Ogilvy" wrote: I don't know, but possibly something like this: Sub ProcTakeOut() Dim rng As Range Dim rng1 As Range Dim rng2 As Range With ActiveSheet If .AutoFilterMode Then .AutoFilterMode = False End If Set rng = Intersect(.Columns(6), .UsedRange).Cells End With rng.AutoFilter Field:=1, Criteria1:="Take Out" Set rng1 = rng.Offset(1, 0).Resize(rng.Rows.Count - 1) On Error Resume Next Set rng2 = rng1.SpecialCells(xlVisible) On Error GoTo 0 If Not rng2 Is Nothing Then rng2.EntireRow.Delete End If rng.AutoFilter End Sub -- Regards, Tom Ogilvy "MESTRELLA29" wrote in message ... OK Tom this was a good Idea but, somthing is not right, wile i am recording the macro and reach Delete row, my excel goes to "Not Responding" and dies... you know why? "Tom Ogilvy" wrote: Select column F, do Data=Filter=Autofilter Filter on Take Out in the drop down in Column F Select all the rows Do edit =Delete Do Data=Filter=Autofilter to remove the filter Turn on the macro recorder while you do the actions manually to get the code to mimic these actions. Adjust the recorded code to make it more general. -- Regards, Tom Ogilvy "MESTRELLA29" wrote in message ... Ok AMIGOS... Here is a macro that goes to column "F" finds the the cell that contains "take out" then deletes the row. The problem is that it takes to long to run. So here is what I want to do. I sorted the "F" column to group all the "take out" cells together, so the macro will start deleting the all rows first then will reach a point when it will no longer find "Take out" cells and stop looking. So now the macro will not have to search the hole list. Is this Possible... Keep In Touch Marco Estrella Range("F:F").Select With Selection Set C = .Find("Take Out", LookIn:=xlValues) If Not C Is Nothing Then firstAddress = C.Address Do Set d = C.Offset(-1, 0) C.EntireRow.Delete Set C = .FindNext(d) Loop While Not C Is Nothing End If End With I am not so sure but I tink I found this macro in http://www.cpearson.com/excel.htm Cool stuff there. |
Take out row...
Is Take Out being produced by a formula like
=if(condition,"Take Out","Keep") If so, you might change it to =If(condition,na(),"Keep") then you could do On Error Resume Next set rng = columns(7).specialcells(xlformulas,xlErrors) On Error goto 0 If not rng is nothing then rng.EntireRow.Delete end if If this would result in more than 8192 separate areas (not cells, but separate non-contiguous areas), then You would have to do it in parts as that is the limit. but this method is usually faster than Autofilter or looping. -- Regards, Tom Ogilvy "MESTRELLA29" wrote in message ... Thanks Tom,,, that did the job, You have reduce the Time of the Macro From 4.27 m to 2.10 m, for now that is Great... "Tom Ogilvy" wrote: I don't know, but possibly something like this: Sub ProcTakeOut() Dim rng As Range Dim rng1 As Range Dim rng2 As Range With ActiveSheet If .AutoFilterMode Then .AutoFilterMode = False End If Set rng = Intersect(.Columns(6), .UsedRange).Cells End With rng.AutoFilter Field:=1, Criteria1:="Take Out" Set rng1 = rng.Offset(1, 0).Resize(rng.Rows.Count - 1) On Error Resume Next Set rng2 = rng1.SpecialCells(xlVisible) On Error GoTo 0 If Not rng2 Is Nothing Then rng2.EntireRow.Delete End If rng.AutoFilter End Sub -- Regards, Tom Ogilvy "MESTRELLA29" wrote in message ... OK Tom this was a good Idea but, somthing is not right, wile i am recording the macro and reach Delete row, my excel goes to "Not Responding" and dies... you know why? "Tom Ogilvy" wrote: Select column F, do Data=Filter=Autofilter Filter on Take Out in the drop down in Column F Select all the rows Do edit =Delete Do Data=Filter=Autofilter to remove the filter Turn on the macro recorder while you do the actions manually to get the code to mimic these actions. Adjust the recorded code to make it more general. -- Regards, Tom Ogilvy "MESTRELLA29" wrote in message ... Ok AMIGOS... Here is a macro that goes to column "F" finds the the cell that contains "take out" then deletes the row. The problem is that it takes to long to run. So here is what I want to do. I sorted the "F" column to group all the "take out" cells together, so the macro will start deleting the all rows first then will reach a point when it will no longer find "Take out" cells and stop looking. So now the macro will not have to search the hole list. Is this Possible... Keep In Touch Marco Estrella Range("F:F").Select With Selection Set C = .Find("Take Out", LookIn:=xlValues) If Not C Is Nothing Then firstAddress = C.Address Do Set d = C.Offset(-1, 0) C.EntireRow.Delete Set C = .FindNext(d) Loop While Not C Is Nothing End If End With I am not so sure but I tink I found this macro in http://www.cpearson.com/excel.htm Cool stuff there. |
All times are GMT +1. The time now is 02:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com