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

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

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

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