Take out row...

[Guest]

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.

 

[Tom Ogilvy]

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.

 

[Guest]

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]

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

 

[Guest]

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]

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.