Delete rows if not as range

[kashyap]

I need to delete rows if sheet1 Column F not equal to Sheet3 G5:G10

 

[kashyap]

I tried below code, but it works very very slow.. Actually there are around 25000+ records..

sub deleterows()
Set MySheet = Sheets("Sheet1")
LR = MySheet.Cells(Rows.Count,"A").End(xlup).Row
For i = LR to 2 Step -1
With Sheets("Sheet3")
If WorksheetFunction.Countif(.Range("A:A"),MySheet.Cells(i,"A"))>0 Then
MySheet.Rows(i).EntireRow.Delete
End If
End With
Next i
End Sub


Any better one..?

 

[kashyap]

Sorry, I used

sub deleterows()
Set MySheet = Sheets("Sheet1")
LR = MySheet.Cells(Rows.Count,"F").End(xlup).Row
For i = LR to 2 Step -1
With Sheets("Sheet3")
If WorksheetFunction.Countif(.Range("G5:G10"),MySheet.Cells(i,"F"))>0 Then
MySheet.Rows(i).EntireRow.Delete
End If
End With
Next i
End Sub

 

[Peter_SSs]

Try this instead. I suggest that you test on a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Delete_Rows()<br><SPAN style="color:#00007F">Dim</SPAN> mySheet <SPAN style="color:#00007F">As</SPAN> Worksheet<br><br>Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">Set</SPAN> mySheet = Sheets("Sheet1")<br><SPAN style="color:#00007F">With</SPAN> mySheet<br>    .Columns("G").Insert<br>    <SPAN style="color:#00007F">With</SPAN> .Range("G1", .Range("F" & Rows.Count).End(xlUp).Offset(, 1))<br>        .Formula = "=ISNUMBER(MATCH(F1,Sheet3!G$5:G$10,0))"<br>        .Value = .Value<br>        mySheet.UsedRange.Sort Key1:=mySheet.Range("G2"), Order1:=xlAscending, _<br>            Header:=xlYes, OrderCustom:=1, MatchCase:=False, _<br>            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal<br>        .AutoFilter Field:=1, Criteria1:=<SPAN style="color:#00007F">True</SPAN><br>        .Offset(1).EntireRow.Delete<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    .Columns("G").Delete<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>