[Solved] Excel VBA, how can I get the MINUS or EXCEPT effect for two sheets in Excel?

by


For me, doing it with VBA is easier.

Sub CreateSubSet()

    Dim WsS As Worksheet                    ' Source
    Dim WsT As Worksheet                    ' Target
    Dim WsR As Worksheet                    ' Reference
    Dim Ref As Range                        ' the Id column in WsR
    Dim Id As Variant                       ' Id lifted from WsS
    Dim Fnd As Range                        ' Find Id in Ref
    Dim Rl As Long                          ' last row (in WsS)
    Dim Rs As Long                          ' Source row
    Dim Rt As Long                          ' Target row
    Dim C As Long                           ' column loop counter

    Set WsS = Worksheets("Sheet2")
    Set WsT = Worksheets("Result")
    Set WsR = Worksheets("Sheet1")

    With WsT
        Rt = .Cells(.Rows.Count, 1).End(xlUp).Row           ' 1 = column A
        Rt = Application.Max(Rt, 2)                         ' start in row 2
    End With

    With WsR
        Rl = .Cells(.Rows.Count, 1).End(xlUp).Row           ' 1 = column A
        Set Ref = .Range(.Cells(2, 1), .Cells(Rl, 1))       ' start in row 2
    End With

    With WsS
        Rl = .Cells(.Rows.Count, 1).End(xlUp).Row           ' 1 = column A
        For Rs = 2 To Rl                                    ' start in row 2
            Id = .Cells(Rs, 1).value
            Set Fnd = Ref.Find(Id, Ref.Cells(Ref.Cells.Count))
            If Fnd Is Nothing Then
                For C = 1 To 2
                    WsT.Cells(Rt, C).value = .Cells(Rs, C).value
                Next C
                Rt = Rt + 1
            End If
        Next Rs
    End With
End Sub

solved Excel VBA, how can I get the MINUS or EXCEPT effect for two sheets in Excel?