I have two sheets Sheet1 and Sheet2 in excel .Sheet 1 has Columns C1,C2,C3,C4,C5.Sheet 2 has Columns C1,C2,C3.Now I have to perform 3 operations.
1.) Delete all rows in Sheet1 where values of Column 1 is not found in Column 1 of Sheet2.
2.) Replace values of C2,C3 of Sheet 2 into C2,C3 of Sheet 1 (C4,C5 remains the same) where value of C1 of Sheet 1 matches C1 of Sheet2.
3.) Append C1,C2,C3 data of Sheet 2 into Sheet 1 where values of C1 in Sheet 2 is not found in C1 of Sheet1(C4,C5 will be blank).
I am able to write the VB code for operation 1.Please help me with operation 2 and operation 3.
Sub delete_selected_rows()
Dim rng1 As Range, rng2 As Range, rngToDel As Range, c As Range
Dim lastRow As Long
With Worksheets("Sheet1")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set rng1 = .Range("A2:A" & lastRow)
End With
Set rng2 = Worksheets("Sheet2").Range("A:A")
For Each c In rng1
If IsError(Application.Match(c.Value, rng2, 0)) Then
'delete incidents which are not in process
If rngToDel Is Nothing Then
Set rngToDel = c
Else
Set rngToDel = Union(rngToDel, c)
End If
End If
Next c
If Not rngToDel Is Nothing Then rngToDel.EntireRow.Delete
End Sub