Trouble with If-Then-Else in a VBA Macro

Originally Posted by TMShucks

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Sorry... Forgot!

Originally Posted by TMShucks

If you are deleting rows, you need to work from the bottom up otherwise the loop will get "out of sync".
Not sure of your logic so I can't really help with what it should actually be.
Regards, TMS

I am not having any trouble with the "sync" of the loop. I am not sure what you mean by logic, and because I am new with writing VBA I will try to be more specific.
I am trying to delete duplicate lines of data by comparing a cell in col A to the adjacent col B cell. ie A2 to B2, A3 to B3... If the values of the two match, then I want to delete that line.
Here is that code that WORKS just fine for that...

Please Login or Register  to view this content.

However, this code deletes the row when both values are blank. (Obviously because they are both blank, the are both equal values)
I do not want them to be deleted when they are blank because I have data in other adjacent columns that may associate with those blank cells.
So I am trying to figure out a way to keep from deleting those rows that have blanks in both col A and B.
I need to add something to the code that says "If the active cell is blank, then end the if statement and go to the next row."

Hope this adds some clarification...

Originally Posted by mikerickson

The first End If is in the wrong place. If you want ActiveCell = Null to do nothing then just do nothing.
Also, CStr(ActiveCell.Value) will never equal Null, although it might = vbNullString

Thank You, both of you!
However mikerickson, I had to replace your " ' End If " with " Cells(ActiveCell.Row + 1, 1).Select "
Otherwise it just went on a loop on the same row.
My new working code is...

Please Login or Register  to view this content.

Just a BONUS if anyone can answer....
To go along with this code, would it be possible to add code that would cut-paste the identical values to a new sheet instead of deleting them, and then give me a msgbox of how many cells were identical, and how many unique values remain? I know that on Excel '07 or '10, there is a delete duplicate button and it tells you how many were deleted and remain, but not sure if that will also copy the deleted values to a new worksheet. I am working on excel '03 at this moment.....

Originally Posted by TMShucks

So, if you have several consecutive rows that meet the deletion criteria, do they all get deleted?

Or, are there any rows later in the cycle that should be deleted but aren't?

Regards, TMS

According to my very recent testing, ALL duplicate rows get deleted.
I think I know where you are coming from.... Lets see if we sync logic...
When A2=B2 the code deletes that row making then next row the active cell. However the code states to go down a line after deletion, which if that line had duplicates it would get skipped...
I have no idea how the code I have works (because it was a copy and paste from some other code) But I have to assume that when it deletes a row of equal values, it skips going down another line and re-compares that new row.
Not sure if any of this makes sense, hard to explain with a 9 month old sitting in my lap. :-)

@TM Shucks,
That works very well! However, it took me a bit to figure out why I didn't see the copied data... I actually had data already existing on Sheet two...
Thankfully your code puts all of the duplicate copied data at the end. :-)
Awesome work!!! I might learn something yet!