Error When Implementing Code: "1004 - Application-defined or object-defined error"

I have been writing a small macro that takes data from a database and trims out the useless data that I get. when I run this code it works for the first third of the trimming. Then when it gets near the end of the list I get the following error:

"1004 - Application-defined or object-defined error"

Here is my code:

Please Login or Register  to view this content.

The line of code that contains the error is:

Please Login or Register  to view this content.

Hopefully you guys can debunk this bug. I have spent sometime looking online for tips as to how it could be fixed with very little luck.

Anyways thanks for any help you provide,
blueblazingdemon

P.s. This is my first post ever, any tips that could improve my posts for future posts is always welcomed.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code in [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

What exactly is the code meant to do?

What's supposed to be trimmed from the database?

One thing you should do is remove all the instances of CStr and replace + with & when you are trying to concatenate.

For example.

Please Login or Register  to view this content.

Also, you should check the values of all the variables, eg k, when you get the error.

You can do that in the Locals Window (View>Locals Window).

I thought k might be the problem.

It looks like your code is moving way past the last row of data and going all the way down to the very last row on the worksheet.

When it gets there it can't go any further, and you'll get the 'Application defined...' error.

I think you need to rearrange the code to prevent that happening.

To start with try finding the last row of data before you start looping.

Then loop from that row upwards, that's kind of standard practice when deleting.

So the loop would look something like this.

Please Login or Register  to view this content.

Now within the loop you should check all 3 columns at the same time to see if the row has to be deleted.

So, if I've got the criteria remotely right, the code would look something like this

Please Login or Register  to view this content.

Does it work for the first two criteria, without the 'PRODUCTION' criteria?