Help with simple delete rows macro for large range

[ejreiter]

Hi. I have a spreadsheet with 60,000 records.

I want to delete a row if it contains a specific value.

I can successfully do it, by looping through each row, 1 by 1, looking for the value and the deleting the row.

It takes a really long time to do it (even with screenupdating off).

Any help would be appreciated to make this more effecient:

for i = 1 to 60000
if activecell.value = "string" then
selecttion.entirerow.delete
else
activecelloffset(1,0).select
endif

 

[VoG]

Hi. Avoid selecting and loop backwards to avoid missing some rows

Sub RemString()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If Range("A" & i).Value = "string" Then Rows(i).Delete
Next i
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

If you don't mind your data being sorted you should find this considerably quicker. It assumes the data has a heading row.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Del_Rows()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.UsedRange<br>        .Sort Key1:=.Cells(2, 1), Order1:=xlAscending, Header:=xlYes, _<br>            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _<br>            DataOption1:=xlSortNormal<br>        .AutoFilter Field:=1, Criteria1:="=string"<br>        .Offset(1).EntireRow.Delete<br>        .AutoFilter<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    MsgBox "Done"<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>