Help deleting a row
 

This macro deletes all rows that on column "F" have the word "Take Out"

It works fine but, it takes to long, and now I need the macro to delete this
and also go to column "G" and delete all that are not "allowance".

I need this fast, the current macro take about 3 to 4 minutes

'Borra los que no sean Customer Validos

Columns("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

Help deleting a row
 

Make one big range out of all of the found cells and the delete the entire
range all at onece similar to this...

dim rngAll as range

Columns("F:F").Select

With Selection

Set c = .Find("Take Out", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
set rngAll = c
Do
set rngAll = Union(c, rngAll)
Set c = .FindNext(c)
Loop While c.address < firstAddress
rngall.delete
End If
End With

(untested but it should be close)
--
HTH...

Jim Thomlinson


"MESTRELLA29" wrote:

This macro deletes all rows that on column "F" have the word "Take Out"

It works fine but, it takes to long, and now I need the macro to delete this
and also go to column "G" and delete all that are not "allowance".

I need this fast, the current macro take about 3 to 4 minutes

'Borra los que no sean Customer Validos

Columns("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

Help deleting a row
 

It is much better to sort your data first based on your deletion criteria. Excel deletes blocks of
rows much more quickly than individual rows, which
you will find out when you have a lot of rows, and a lot of rows to be deleted interspersed.

For your problem, try the code below.

HTH,
Bernie
MS Excel MVP

Sub DeleteTakeOutAndAllowance()
Dim myRows As Long
Range("A1").EntireColumn.Inser*t
Range("A1").FormulaR1C1 = _
"=IF(RC[6]=""Take Out"",""Trash"",""Keep"*")"
myRows = ActiveSheet.Range("G65536").En*d(xlUp).Row
Range("A1").Copy Range("A1:A" & myRows)
With Range(Range("A1"), Range("A1").End(xlDown))
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending
Columns("A:A").Find(What:="Tra*sh", After:=Range("A1")).Select
Range(Selection, Selection.End(xlDown)).EntireR*ow.Delete

Range("A1").FormulaR1C1 = _
"=IF(RC[7]<""Allowance"",""Trash"",""Keep"*")"
myRows = ActiveSheet.Range("H65536").En*d(xlUp).Row
Range("A1").Copy Range("A1:A" & myRows)
With Range(Range("A1"), Range("A1").End(xlDown))
.Copy
.PasteSpecial Paste:=xlValues
End With
Cells.Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending
Columns("A:A").Find(What:="Tra*sh", After:=Range("A1")).Select
Range(Selection, Selection.End(xlDown)).EntireR*ow.Delete

Range("A1").EntireColumn.Delet*e
End Sub

"MESTRELLA29" wrote in message
...
This macro deletes all rows that on column "F" have the word "Take Out"

It works fine but, it takes to long, and now I need the macro to delete this
and also go to column "G" and delete all that are not "allowance".

I need this fast, the current macro take about 3 to 4 minutes

'Borra los que no sean Customer Validos

Columns("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

Help deleting a row
 

Sorry I noticed a goof in my coding...

rngall.delete
should be
rngall.entirerow.delete

Sorry
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

Make one big range out of all of the found cells and the delete the entire
range all at onece similar to this...

dim rngAll as range

Columns("F:F").Select

With Selection

Set c = .Find("Take Out", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
set rngAll = c
Do
set rngAll = Union(c, rngAll)
Set c = .FindNext(c)
Loop While c.address < firstAddress
rngall.delete
End If
End With

(untested but it should be close)
--
HTH...

Jim Thomlinson


"MESTRELLA29" wrote:

This macro deletes all rows that on column "F" have the word "Take Out"

It works fine but, it takes to long, and now I need the macro to delete this
and also go to column "G" and delete all that are not "allowance".

I need this fast, the current macro take about 3 to 4 minutes

'Borra los que no sean Customer Validos

Columns("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