Simple problem with comparison

[claytonmtaylor]

Hi,
I have a large excel with a couple thousand rows of projects. In column R of each row is the location of each project. I have a list of several acceptable locations, and I need delete any project that either isn't in one of those locations or contains no information in column R.
How I attempted to do this was:

Please Login or Register  to view this content.

However, when I attempted to run it as a test, I would get a message saying "Syntax Error".
What am I doing wrong?
Also, if you want extra points to help me out, how would I compact the rows together after all unnecessary rows have been deleted?

[shg]

Welcome to the board.

Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

Thanks.

[claytonmtaylor]

Welcome to the board.

Please take a few minutes to read the forum rules, and then edit your post to add CODE tags.

Thanks.

Thanks, but the forum rules don't seem to be in english, could you tell me how to do that, or help me with my initial problem?

[shg]

One way:

Please Login or Register  to view this content.

Remember that text comparisons in VBA are case-sensitive by default.

[claytonmtaylor]

This works fine, but I need it to be able to read certain strings and not delete those.

[shg]

Please Login or Register  to view this content.

You need to iterate backward when you're deleting rows.

[claytonmtaylor]

You need to iterate backward when you're deleting rows.

Good point, but how would I make something like this work?

Please Login or Register  to view this content.

[shg]

How about explaining in words what you're trying to do?

[claytonmtaylor]

I thought I was. I have a very large excel with several thousand rows that each represent a project. Each project has a location (Column R). I need to delete any project that either isn't in West Texas, or isn't marked by location, or has an empty column R.

[jeffreybrown]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[shg]

So did you try the code in post#6?

[claytonmtaylor]

It gives me an error saying "Application-defined or object defined error"

[shg]

Time for post#10.

[claytonmtaylor]

I've got it. This is the code I used, I was getting hung up because of an infinite loop that I fixed.

Please Login or Register  to view this content.

Now I've got to sort the data by date, I'm sure I can figure out how to do that.

[jeffreybrown]

Maybe this thread can get you started on the sort you desire

https://www.excelforum.com/excel-pro...ort-marco.html

Just remove one of the SortFields if you are sorting by one column

[claytonmtaylor]

Thank you!

[jeffreybrown]

You are very welcome. Happy we could help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.