Fastest way to delete 20k+ rows

[Blunder1]

Hi All,

My sheet contains anyting from 20,000 to 40,000 rows of data. Withing this data i need to delete 95% of this based on cells values in columns M. The values in column M is comes from a look up, and anything with an #N/A value needs the entire row to be deleted. I've tried copying to another sheet, filters etc but anything i try can take ages to run. I have 7 worksheets in the workbook all the same size then have to run this through 60 workbooks, so i'm looking at over a days to run the code providing it doesn't crash my PC!

Does anyone have any suggestion how i can do this quicker?

Thanks in advance

Blunder

 

[Jon von der Heyden]

Sort the table according to column M so that all of the #N/As are in a contiguous range.

Next highlight column M
F5
Special
Formulas > Errors
Ok

Then delete the rows (ctl+-) and choose rows when prompted.

 

[RoryA]

If you can sort the data on column M first, then you will only be deleting one block of cells and that should be faster. You could also use ClearContents instead of deleting the rows.

 

[VoG]

You could try like this. It would help if you sorted by column M first.

Sub test()
Columns("M").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
End Sub

 

[Blunder1]

Thank you all, this has been very helpful.

Thanks again

Blunder

 

[mirabeau]

Hi,

There's been a number of VBA codes on this forum to do that sort of task, fast.
A couple of recent threads maybe useful are:

http://www.mrexcel.com/forum/showthread.php?t=569742
http://www.mrexcel.com/forum/showthread.php?p=2809843

You can also often use Advanced Filter, which is OK if the number of rows isn't too large, say not more than 35,000 or so.

Any method involving row-by-row deletion wouldn't be expected to be very fast.