Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jul 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you to all who replied. I think I may have left out part of the equation. I am editing my question.

    You have two (2) workbooks. Both workbooks have some of the same data in multiple columns in rows. Using the same column in each workbook, if there are duplicates from each workbook, delete the duplicate data in the entire row in the second workbook.

    Thanks again!

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This one clears data in the cell only.
    Code:
    Sub deleteDupes2Books()
    Dim wb1 As Workbook, wb2 As Workbook, ws1 As Worksheet, ws2 As Worksheet
    Dim rKeep As Range, lRow As Long, i As Long, j As Integer
    Set wb1 = Workbooks("Book1.xls")
    Set wb2 = Workbooks("Book2.xls")
    Set ws1 = wb1.Sheets(1)
    Set ws2 = wb2.Sheets(1)
    Application.ScreenUpdating = False
    lRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
    j = 1
    On Error Resume Next
    Do Until ws1.Cells(2, j) = ""
        Set rKeep = ws1.Range(ws1.Cells(2, j), ws1.Cells(Rows.Count, j).End(xlUp))
        For i = lRow To 2 Step -1
            If Application.WorksheetFunction.Match(ws2.Cells(i, j), rKeep, 0) > 0 Then
                If Err.Number <> 1004 Then
                    ws2.Cells(i, j).ClearContents
                End If
                Err.Clear
            End If
        Next
        j = j + 1
    Loop
    Application.ScreenUpdating = True
    End Sub
    This one deletes the entire row if there is a duplicate.
    Code:
    Sub deleteDupes2BooksRows()
    
    Dim wb1 As Workbook, wb2 As Workbook, ws1 As Worksheet, ws2 As Worksheet
    Dim rKeep As Range, lRow As Long, i As Long, j As Integer
    Set wb1 = Workbooks("Book1.xls")
    Set wb2 = Workbooks("Book2.xls")
    Set ws1 = wb1.Sheets(1)
    Set ws2 = wb2.Sheets(1)
    Application.ScreenUpdating = False
    lRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row
    j = 1
    On Error Resume Next
    Do Until ws1.Cells(2, j) = ""
        Set rKeep = ws1.Range(ws1.Cells(2, j), ws1.Cells(Rows.Count, j).End(xlUp))
        For i = lRow To 2 Step -1
            If Application.WorksheetFunction.Match(ws2.Cells(i, j), rKeep, 0) > 0 Then
                If Err.Number <> 1004 Then
                    ws2.Cells(i, j).EntireRow.Delete
                    lRow = lRow - 1
                End If
                Err.Clear
            End If
        Next
        j = j + 1
    Loop
    Application.ScreenUpdating = True
    End Sub

  3. #3
    New Lounger
    Join Date
    Jul 2005
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    To: Mbarron:

    Thank you again. Is this a Macro or Visual Basic?

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    Macros are a method of writing a VB procedure.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •