Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting duplicate rows
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting duplicate rows
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting duplicate rows
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 "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting duplicate rows
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA for deleting duplicate rows | Excel Programming | |||
Deleting duplicate rows | Excel Discussion (Misc queries) | |||
Deleting Duplicate Rows | Excel Programming | |||
Deleting Duplicate Rows | Excel Programming | |||
Deleting Duplicate Rows | Excel Programming |