Deleting duplicate rows

[Guest]

I was learning excel programming from microsoft's training site. I wanted to
test write a programme to delete rows having duplicate data. The data table
has 10 rows on columns B and C. Where the data on both columns B and C are
repeated in subsequent rows ( once or more than once), such duplicate rows to
be deleted. I wrote the following macro and ran it:
--------
Sub DuplicateRowRemover()
x = 3
y = 4
Do While Cells(x, 2).Value <> " "
Do While Cells(y, 2).Value <> ""
If (Cells(x, 2).Value = Cells(y, 2).Value) _
And (Cells(x, 3).Value = Cells(y, 3).Value) Then
Cells(y, 2).EntireRow.Delete
Else
y = y + 1
End If
Loop
x = x + 1
y = x + 1
Loop

----------

The programme is working correctly. All duplicate rows were deleted and
only unique records remained. However, at the end of running the programme,
I get an error message. The yellow debug line was the second 'Do while '
statement. the value of 'y' when I take the cursor near it is showing 65537.
As the Do while should have stopped at the end of the 10th row as 11th
onwards were blank ( i.e., " " as shown in Do While statement), I am puzzled
why this is happening. Can any one help ? Thanks in advance to volunteer.

Balan

 

[Guest]

The space between the quote marks is probably the culprit.

Do While Cells(x, 2).Value <> " "
Do While Cells(y, 2).Value <> ""

Even so, it was pointed out to me yesterday that it better to work from the
bottom up when deleting rows, since the rows are automatically shifted upward
as each row is deleted and this leaves room for rows to be skipped. The data
base also needs to be sorted so that all duplicates will be adjacent to each
other. Good luck on your project.

 

[Guest]

Mr JLGWhiz
Many thanks! The quote marks were the culprit. The extra space between the
quotes on the outer Do While was making it to search all records upto row
65537. When I removed it and inserted a space between the quotes in the
second (inner) Do While, I found it stopped with the first record and was
complaining about its inability to proceed further. By removing the space
between the quotes in both the Do's I found it working very well. Regarding
your suggestion that removing rows bottom up, I shall test. But this one has
not given me any problem as I had deliberately kept some data below the main
data after introducing a blank row. Thanks once again.

Balan

 

[Guest]

Mr JGGWhiz

One more point I wish to make. Earlier I had seen the difference between
the two quote marks. I removed the space and tried and again introduced
space and tried and in both the case I failed. Now I have discovered that I
have to close the VB editor screen to stop the debugger and then only retry.
The changes will take effect only then. (I am writing this so that if any
novice to Excel Programming like me happens to see this post , he should also
know about this). Thanks.

Balan