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
"JLGWhiz" wrote:
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.
"Balan" wrote:
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