Entire row deleting, need to make it faster

[mlarsen]

Hello, I have a Sub here for deleting any entire row, where "http" is NOT found throughout column B. There are about 45,000 rows of data that its searching and its taking a very long time. Is there a way I can change the current code to speed up the process, the code is below. Thanks!

Mark

Sub DeleteRowsHttp()

Application.ScreenUpdating = False
For Each Cell In Range("B:B")
If InStr(1, Cell.Value, "http") = 0 Then
Cell.EntireRow.Delete
End If
Next Cell
Application.ScreenUpdating = True

End Sub

 

[xlbo]

yes - don't loop and make sure calculations are turned off - even if you did have to loop, you should never loop forwards when deleting data as it messes up pointer references - always delete data from the bottom up using
For i = lastrow to 1 step -1
type syntax - however, that is beside the point

typed straight out - not tested)

application.calculation = xlcalculationmanual

lRow - cells(65536,1).end(xlup).row

Range("B1:B" & lRow.autofilter Field:=1, Criteria1:<>"=http*"

Range("B2:B" & lrow).entirerow.delete

Range("B1:B" & lRow.autofiltermode = false

application.calculation = xlcalculationautomatic

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[mlarsen]

huh, yea for some reason that code keeps giving me compile errors...

 

[xlbo]

as I said - typed without testing - it was meant more to give you the idea that you can do it using autofilter

probably want to change

Range("B1:B" & lRow.autofiltermode = false

to

ACTIVESHEET.autofiltermode = false

If you're still having probelms, try recording the actions with a macro...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[PHV]

In fact use the macrorecorder to see the code generated when you autofilter column B with criteria: doesn't contain http and then delete the visible rows.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[combo]

Another option to spped up deleting is finding all cells that match criteria and next delete rows in one command. The steps:
1. first cell found: Set CellsFound=Cell
2. other cells: Set CellsFound=Union(CellsFound,Cell)
3. delete: CellsFound.EntireRow.Delete

combo

 

[xlbo]

revised with correct syntax (cos I made a few typos as well)

Application.Calculation = xlCalculationManual

lRow = Cells(65536, 2).End(xlUp).Row

Range("B1:B" & lRow).AutoFilter Field:=1, Criteria1:="<>http*"

Range("B2:B" & lRow).EntireRow.Delete

ActiveSheet.AutoFilterMode = False

Application.Calculation = xlCalculationAutomatic

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question