Delete rows if not as range

K

Kashyap

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

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,"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


Any better one..?
 
J

Jacob Skaria

Dear Kashya, try replacing the CountIF function with Match.

If this post helps click Yes
 
J

Jacob Skaria

Dear Kashya

I have not done any major changes to your code. Please try and feedback.

Sub deleterows()
Application.ScreenUpdating = False
Set MySheet = Sheets("Sheet1")
Set MyRange = Sheets("Sheet3").Range("G5:G10")
LR = MySheet.Cells(Rows.Count, "F").End(xlUp).Row
For i = LR To 2 Step -1
If WorksheetFunction.CountIf(MyRange, MySheet.Cells(i, "F")) > 0 Then
MySheet.Rows(i).EntireRow.Delete
End If
Next i
Application.ScreenUpdating = True
End Sub

If this post helps click Yes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top