Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
delete row and 3 rows below by range selected | Excel Programming | |||
Help with filter Range & delete rows | Excel Programming | |||
Auto delete rows within a specified range | Excel Programming | |||
Delete Unwanted Rows (Help with Range) | Excel Programming | |||
Delete a range of rows ? | Excel Programming |