Copy rows which have a result after applying the LOOKUPV(filter them from #N/A)

[vbanew98]

I am working with an excel which has about 500000 rows. I have one sheet called "B" where is all the info and I only need the rows where the column Y contains text, not de #N/A from the LOOKUP. I have to copy the rows with info, to another sheet called "A". I used this code for the same process

On Error Resume Next Columns("Y").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete On Error GoTo 0

But in this case, there are many rows so it takes 5 minutes(not worthy)

I only have 3000 rows with non NA, so I thought it will be easier to filter them and copy to "A" the entire row(the column A from the row in "B" it's not necessary, and the destination sheet "A" the column A has to be empty). I don't know how to do it, i'm new in this language, thank you

 

[bebo021999]

So weird, no reason that "SpecialCells method" works so slow!
Iit took me 0.48 seconds with almost 500,000 VLOOKUP formula in column Y with "N/A"

Sub del()
Dim t
t = Timer
On Error Resume Next
Columns("Y").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
On Error GoTo 0
MsgBox Timer - t
End Sub

Could you share the whole code?

 

[vbanew98]

Thanks bebo021999 for spending your time on it. It's a laptop provided by the company, I don't know a lot about computers but I supposed it is power limited. Regards

 

[vbanew98]

So weird, no reason that "SpecialCells method" works so slow!
Iit took me 0.48 seconds with almost 500,000 VLOOKUP formula in column Y with "N/A"

Sub del()
Dim t
t = Timer
On Error Resume Next
Columns("Y").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
On Error GoTo 0
MsgBox Timer - t
End Sub

Could you share the whole code?

The whole code is:

Sub AutoDelete()
Worksheets("B").Activate
On Error Resume Next
Columns("Y").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
On Error GoTo 0
End Sub

 

[bebo021999]

I could not find any wrong process in that full code
Normally, using specialcells method is best way to delete rows at once (in bulk) !