Deleting Rows

[jacqui]

I am using the autofilter method to delete unwanted rows
from my datafile. The autofilter filters the first
criteria, deletes rows and then I repeat the cycle again
using a different criteria value. My code is below and it
works but is there a tidier way of writing it? Can anyone
help?
Many thanks
Jacqui

Sub format_DeleteRows_PersComm()

Dim lLastRow As Long
Dim Rng As Range

Application.ScreenUpdating = False
Rows(1).Insert
Range("F1").Value = "temp"

With ActiveSheet
.UsedRange
lLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Range("F1", Cells(lLastRow, "F"))
Rng.AutoFilter Field:=1, Criteria1:="Pers Total"
Rng.SpecialCells
(xlCellTypeVisible).EntireRow.Delete
.UsedRange
End With

Rows(1).Insert
Range("F1").Value = "temp"

With ActiveSheet
.UsedRange
lLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Range("F1", Cells(lLastRow, "F"))
Rng.AutoFilter Field:=1, Criteria1:="Misc Total"
Rng.SpecialCells
(xlCellTypeVisible).EntireRow.Delete
.UsedRange
End With

Range("F:F").Select
Selection.EntireColumn.Delete


End Sub

 

[Ron de Bruin]

You can use a array

Maybe you like this example that use Find
You can use more words in the array

http://www.rondebruin.nl/delete.htm#Find

 

[Jacqui Fenn]

Ron,

Thanks for your reply. I looked up your web address and found various
examples but now I have a couple of questions. Based on a datafile that
contains at least 3,000 rows which of the two methods is faster, ie Find
using the Array or the AutoFilter method. I agree that by using the
Find example you can add a number of string values to the Array but is
this as quick as AutoFilter?
Secondly, if AutoFilter is the most efficient of the two then how could
I adapt my original code so that criteria1 contains a value, deletes the
rows and then does a repeat with another value in criteria1. I saw your
example but this is based on just 1 delete value of "ron". What if I
wanted to have a deletevalue1 = "ron", then deletevalue2 = "jacqui".
How would you rewrite the code?

Many thanks
Jacqui

 

[Ron de Bruin]

Hi Jacqui

Speed depend on a lot of things
Don't look at the speed only

The best way is to loop through all rows
You have much more control(use Trim, case sensetive......)
See the first example on the page

If you want to use filter, you can use two citeria.

Columns("A").AutoFilter Field:=1, Criteria1:="jacqui", Operator:=xlOr, _
Criteria2:="ron"