Excel 2007 : Compare sheets and view removed rows

**barboa** · 05-30-2012, 11:21 AM

I can not figure out how to compare two different stylesheets to view the cells that have been removed. I have a sheet with a list of users and thier attributes and some needed to be deleted through the database. I downloaded a copy of the updated db and need to view a list of all of the accounts that were deleted/removed in the excel sheet. If any one can help I would greatly.

**arlu1201** · 05-30-2012, 11:28 AM

Sure, attach a sample excel file and i can help you out.

**barboa** · 05-30-2012, 11:40 AM

Book1.xls Book2.xls Here are the files thank you for the help. the first is the original the second is the one with deletes.

**arlu1201** · 06-01-2012, 12:01 PM

Try this code

Option Explicit

Sub compare_bks()
Dim lrow As Long, lrow1 As Long, i As Long

With ThisWorkbook.Worksheets(1)
    .Range("H1").Value = "Book"
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("H2:H" & lrow).Value = "1"
    lrow1 = .Range("H" & .Rows.Count).End(xlUp).Row
End With
    
With Workbooks("Book2.xls").Worksheets(1)
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("A2:G" & lrow).Copy ThisWorkbook.Worksheets(1).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End With

With ThisWorkbook.Worksheets(1)
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    .Range("H" & lrow1 + 1 & ":H" & lrow).Value = "2"
End With
    
With ThisWorkbook.Worksheets(1)
    .Sort.SortFields.Add Key:=Range("B:B") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .SetRange Range("A:H")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = 2 To lrow
        If .Range("B" & i).Value <> .Range("B" & i + 1).Value Then
            .Range("A" & i & ":H" & i).Interior.Color = 65535
        Else
            i = i + 1
        End If
    Next i

End With

End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code (in this case book1.xls)
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste.

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button.