Type Mismatch Trying to Delete Rows

I have a macro that I've been using in order to clean up these text reports I receive from our ancient distribution management system. This macro has worked perfectly except for this customer, because their name has the correct amount of characters to throw a wrench into my code. The columns are separated in such a way that "- INDUS" is in its own cell, and Excel thinks this is a formula since it starts with the 'minus' symbol. This shows up as "#NAME?" in the cells where this is present (all in Column C), and gives me a type mismatch error 13 whenever I run the macro.

I have included the code parts that may need troubleshooting below. Perhaps a formatting change would do the trick in the Text To Columns portion (I haven't yet found a working method). Note, however, that the columns must be in their current positions, or the other data will not be properly sorted. Or maybe some sort of error handling code needs to be included where the columns are deleted. Either way, I am unsure of the fix, and would greatly appreciate any help.

Code for my text to columns:

Please Login or Register  to view this content.

Code to Delete Rows:

Please Login or Register  to view this content.

I have also attached a spreadsheet that illustrates the before and after on the trouble-causing row of text to columns.


Edit: Looks like I solved my own problem (why would I not have thought to do this?), as if I just use On Error Resume Next before the code, then the macro finishes properly. I'm not quite sure if this is the ideal solution though, so if anyone has anything better please let me know. Otherwise I will consider this solved.

Deleting rows one at a time can be very inefficient, especially on larger datasets.

Based on the criteria I see you are using, I would suggest you consider using the AutoFilter. IN Excel 2007 I believe it's found under Data > Table.

Anyway, you should be able to apply a custom filter to column E testing for both "" and "OLD PRICE", then delete all visible rows, then filter column C for blanks and delete all visible rows and you're done, even if the dataset was huge, this would be very quick.

It appears you may not have a "title row" at the top of your data, which would be necessary to use an AutoFilter, easy to add and then remove

Please Login or Register  to view this content.