I'm new to VBA and would appreciate help modifying this code. I'm using the code below in the workbook attached. It removes duplicates in Sheet1 and copies values located in Sheet1B&C into Sheet2A&B. The problem is when it copies the data over it creates a ton of duplicates in Sheet2. I need to modify the code to only copy over data from Sheet1B&C to Sheet2A&B if it's not already located in Sheet2 e.g. if the data in Sheet1B3&C3 are already located in Sheet2A&B as a pair in the same row it DOESN'T need to be copied over. I need to eliminate duplicate values in A&B of Sheet2; only unique values should remain.
I need the data I'm copying from Sheet1 to be copied over as pairs of data in the same row e.g. A2&B2, A3&B3. If duplicates ever appear in Sheet2 they need to be deleted based on whether or not there's data in D and/or C of the same row. If there's data in C and/or D I need the duplicate without data in C and/or D to be deleted.
I then need to include a conditional format that highlights and row A:D in Sheet2 if C and/or D is blank.
Sub Macro1()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, c As Range, loc As Range
Set sh1 = Sheet1
Set sh2 = Sheet2
lr = sh1.Cells(Rows.Count, 1).End(xlUp).Row
sh1.Range("A2:D" & lr).Sort Range("A1"), xlAscending, Range("B1"), , , Range("C3"), xlAscending, Header:=xlNo
For i = lr To 3 Step -1
With sh1
If .Cells(i, 1) = .Cells(i - 1, 1) And .Cells(i, 2) = .Cells(i - 1, 2) _
And .Cells(i, 3) = .Cells(i * 1, 3) Then
If .Cells(i, 4).Value > .Cells(i - 1, 4).Value Then
.Cells(i - 1, 4).EntireRow.Delete
Else
.Cells(i, 4).EntireRow.Delete
End If
End If
End With
Next
Set rng = Intersect(sh1.Range("B2").Resize(Rows.Count - 2, 1), sh1.UsedRange)
For Each c In rng
Set loc = sh2.Range("A2", sh2.Cells(Rows.Count).End(xlUp)).Find(c.Value)
If Not loc Is Nothing Then
If loc.Offset(0, 1) <> c.Offset(0, 1) Then
c.Resize(1, 2).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
End If
Else
c.Resize(1, 2).Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
End If
Next
End Sub
0SAMPLE.xlsm
Bookmarks