VBA to Compare two different worksheets and copy missing rows

[cscotty]

Hello All.

I've been wrecking my brain for the past few days on how to write this vba script but it just wont come to me.

I have two worksheets in the same workbook. Sheet1 and Sheet2.

Sheet1 is consistently updated with new information and made available via Data Query . Sheet2 obtains its data from Sheet1, but Sheet2 is updated by my team. When Sheet1 is updated, it will contain the rows has already been copied to sheet 2. Therefore, I would like some help with a vba script that will compare a single column in Sheet1 (Column A) with a single column in Sheet2 (Column A). If the value does not exist, then I would like to copy specific cells from Sheet1 to Sheet2, for example, Columns A, C, E).

Unfortunately the code I have so far is pretty messy (I'm still learning vba) but if any help can be provided I'd appreciate it. I have attached a picture of the expected results.

Thanks so much in advance for your help!

Sub CompareTwoColumns()
      Dim col1 As Range
      Dim col2 As Range
      Dim prod1 As String
      Dim prod2 As String
      Dim lr As Long
      Dim incol1 As Variant
      Dim incol2 As Variant
      Dim r As Long
      Dim ws1 As Worksheet
      Dim ws2 As Worksheet
      
      Set ws1 = Sheets("Sheet1")
      Set ws2 = Sheets("Sheet2")
      Set col1 = Sheets("Sheet1").Columns("A")
      Set col2 = Sheets("Sheet2").Columns("A")
      
    lr = Columns("B:C").SpecialCells(xlCellTypeLastCell).Row

'Select first empty cell in column 2
With ws2

For Each cell In ws2.Columns(2).Cells
        If IsEmpty(cell) = True Then cell.Select: Exit For
    Next cell

End With

'Set empty cell variable
Dim nextcell As Range
Set nextcell = ActiveCell

 With ws1
      For r = 2 To lr
          prod1 = Cells(r, col1.Column).Value
          prod2 = Cells(r, col2.Column).Value
   
          'check if prod1 is in col2
          If prod1 <> "" Then
              Set incol2 = col2.Find(prod1)
              If incol2 Is Nothing Then
                  'Cells(r, col1.Column).Interior.Color = vbYellow
                .Range(.Range("A2"), .Columns("A").Cells(.UsedRange.Rows(.UsedRange.Rows.Count).Row)).copy Destination:=nextcell
                .Range(.Range("C2"), .Columns("C").Cells(.UsedRange.Rows(.UsedRange.Rows.Count).Row)).copy Destination:=nextcell.Offset(0, 2)
                .Range(.Range("E2"), .Columns("E").Cells(.UsedRange.Rows(.UsedRange.Rows.Count).Row)).copy Destination:=nextcell.Offset(0, 4)
              End If
          End If
   
Next
End With
End Sub

 

[cscotty]

Are you talking about a formula cell with many levels of nesting? Because if we are talking about text content, there can be much more characters (I will attach a fragment of a screenshot). I have not checked, but on the Microsoft website even for excel 2010 it says Total number of characters that a cell can contain - 32,767 characters.

View attachment 87197

Exactly, a formula cell with many levels of nesting. Specifically, I have used CONCAT in cell A5 to combine the values in (for example), B5:F5. I then use A5 as a primary key based on the CONCAT values. If any value in the range changes, then the 'primary key' would change. Then when I run the compare, a new row with the changes will appear. All is working well except for the 255 character limit when I CONCAT too many values.

Thanks!