visual question: delete entire rows in Excel Macro

chencharlie1
chencharlie1 used Ask the Experts™
on
I am not a VB programmer.  Can anyone help me to modify it to delete the entire rows if it found duplicate.  The existing sample from Microsoft can only delete the cell, not the entire row.
Thanks

Sample 2: Compare two lists and delete duplicate items
The following sample macro compares one (master) list against another list, and deletes duplicate items in the second list that are also in the master list. The first list is on Sheet1 in the range A1:A10. The second list is on Sheet2 in the range A1:A100. To use the macro, select either sheet, and then run the macro.
Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through (list that will be deleted).
iListCount = Sheets("sheet2").Range("A1:A100").Rows.Count

' Loop through the "master" list.
For Each x In Sheets("Sheet1").Range("A1:A10")
   ' Loop through all records in the second list.
   For iCtr = 1 To iListCount
      ' Do comparison of next record.
      ' To specify a different column, change 1 to the column number.
      If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then
         ' If match is true then delete row.
         Sheets("Sheet2").Cells(iCtr, 1).Delete xlShiftUp
         ' Increment counter to account for deleted row.
         iCtr = iCtr + 1
      End If
   Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"
End Sub

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
chencharlie1,

The solution to your problem lies in the line that says

    Sheets("Sheet2").Cells(iCtr, 1).Delete xlShiftUp

change it to

    Sheets("Sheet2").Cells(iCtr, 1).EntireRow.Delete

so the final code is:

Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through (list that will be deleted).
iListCount = Sheets("sheet2").Range("A1:A100").Rows.Count

' Loop through the "master" list.
For Each x In Sheets("Sheet1").Range("A1:A10")
   ' Loop through all records in the second list.
   For iCtr = 1 To iListCount
      ' Do comparison of next record.
      ' To specify a different column, change 1 to the column number.
      If x.Value = Sheets("Sheet2").Cells(iCtr, 1).Value Then
         ' If match is true then delete row.
         Sheets("Sheet2").Cells(iCtr, 1).EntireRow.Delete
         ' Increment counter to account for deleted row.
         iCtr = iCtr + 1
      End If
   Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"

End Sub


/sylikc

Author

Commented:
Thanks for your quick response.
I have just tested and I think it only compare the first character of each rown in Column A.
What I am looking for is to compare the entire content of Column A for each row?
Can that be done?
For example:
If
A1= 123456 in sheet one
A1=123456 in sheet two
Then
Delete the entire row containing the 123456 in sheet two
End
Commented:
Change one line of sylikc's code to:

If Trim(x.Value) = Trim(Sheets("Sheet2").Cells(iCtr, 1).Value) Then

(first IF statement in second (nested) FOR loop)

jr
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
I made the modification on the initial demo code that you provided..

I created two sheets, and filled them with values.  I don't seem to notice that it only compares one character.  What type of fields do you have?  If you have spaces in your text fields, then jruhe's comment helps.  However, if you are looking for case-insensitive comparison, the code needs to be modified further.

/sylikc

attached code is now case-insensitive and ignores leading/trailing spaces.
Sub DelDups_TwoLists()
Dim iListCount As Integer
Dim iCtr As Integer

' Turn off screen updating to speed up macro.
Application.ScreenUpdating = False

' Get count of records to search through (list that will be deleted).
iListCount = Sheets("sheet2").Range("A1:A100").Rows.Count

' Loop through the "master" list.
For Each x In Sheets("Sheet1").Range("A1:A10")
   ' Loop through all records in the second list.
   For iCtr = 1 To iListCount
      ' Do comparison of next record.
      ' To specify a different column, change 1 to the column number.
      If UCase(Trim(x.Value)) = UCase(Trim(Sheets("Sheet2").Cells(iCtr, 1).Value)) Then
         ' If match is true then delete row.
         Sheets("Sheet2").Cells(iCtr, 1).EntireRow.Delete
         ' Increment counter to account for deleted row.
         iCtr = iCtr + 1
      End If
   Next iCtr
Next
Application.ScreenUpdating = True
MsgBox "Done!"

End Sub

Open in new window

Commented:
Yes, Sylikc--check out chencharlie1's comment ID 26441774--it shows:
A1= 123456 in sheet one
A1=123456 in sheet two
Note the space in front of the first "123456" in the first A1 statement.

Author

Commented:
Yes, I need to igonre any lead space in the field.
They should be consider the same if there is space in the front.
Thanks for your assistance.
I really appreciate it.
Thanks again.

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial