If/Then Loop to find Matches in Column

**Engineers08** · 06-03-2008, 02:45 PM

Ok, this one shouldn't be that hard.

I'm looking to go down column A, and find all of the doubled (or tripled, quad... ect) values. I originally would manually insert a cloumn, and write an "if" function to place an X if the cell R[-1]C[-1] was equal to the cell RC[-1]. I'd then run the macro to delete them. However, I want to have it all automated, so i'm trying to do that here in the top of the macro here.

(The if/then loop)

It doesn't work

. The erasing of rows that have X's works fine. It's just that I cant get the macro to place X's.

Please Login or Register  to view this content.

Thanks,

`Ryan

**SOS** · 06-03-2008, 05:33 PM

Hi engineers08,

Assuming that Row 1 contains headers I have adjusted your code and it should work:

Please Login or Register  to view this content.

**SOS** · 06-03-2008, 06:37 PM

But wait,

In your example Username in Cell A3 is 100100523 with a FirstLaunch in Cell B3 of 13/03/2008 and the same Username is in Cell A4 with a FirstLaunch in Cell B4 of 03/04/2008:

The code you wrote (and that I modified) will insert a column and place an X in Cell B3 (because the value of A3 = the value of A4). The rest of the code will then delete the whole row 3. Is that what you want?

I should also have added another line to the code to delete the column that was added to house the "X's"

Please Login or Register  to view this content.

**Engineers08** · 06-04-2008, 10:11 AM

SOS, you're awesome!

Just checked it on my sample sheet, then made the changes neccisary to apply it to the real deal.

I have one more question here...

Say I had another column, that was already populated by three different variables. We can call them "Complete", "incomplete", and "not started". If I wanted to delete all the rows that had "incomplete" or "not started" in them, how would I go about including that in my row deletion function.

Would I have to write 2 extra autofilter statements? Or could I somehow include them in there? I think it would be easier if we did it before the "X" delete function.

I've updated my example sheet, please take a look?

Thanks a lot!
`Ryan

**SOS** · 06-04-2008, 11:23 AM

Engineers08

Updated file attached which should do what you need. Let me know if it doesn't.

**Engineers08** · 06-04-2008, 02:04 PM

I've messed with it for a while. It just seems to erase all of the data when I put it into my sheet.

It also gives me an error on the line:

Please Login or Register  to view this content.

**SOS** · 06-05-2008, 02:55 AM

Hi Engineers08

Looked at it again and tweaked the code to check if Autofilter is on or off and switch it on or off accordingly

It seems to work for me.

Please Login or Register  to view this content.

**Engineers08** · 06-05-2008, 09:17 AM

Yeah, my bad. I had a bad day on other projects and forgot to change the autofilter feild ID in when I ported it to my new doc. I also had to add another filter before adding the "X"s to make sure I got em all.

Thanks a million, you've not only helped me out, but taught me about the autofilters.

Cheers!
`Ryan