Delete rows if not as range
 

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..?

Delete rows if not as range
 

Dear Kashya, try replacing the CountIF function with Match.

If this post helps click Yes
---------------
Jacob Skaria


"Kashyap" wrote:

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..?

Delete rows if not as range
 

Hi, would you please help me out with the formula..



"Jacob Skaria" wrote:

Dear Kashya, try replacing the CountIF function with Match.

If this post helps click Yes
---------------
Jacob Skaria


"Kashyap" wrote:

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..?

Delete rows if not as range
 

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
---------------
Jacob Skaria


"Kashyap" wrote:

Hi, would you please help me out with the formula..



"Jacob Skaria" wrote:

Dear Kashya, try replacing the CountIF function with Match.

If this post helps click Yes
---------------
Jacob Skaria


"Kashyap" wrote:

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..?