Copy If Not Exists

[billandrew]

Good Afternoon all

Looking to copy values [to another worksheet] from Column H which are not in Column T

This Script highlights column H values not in Column T - hoping for some insight how to attain the above request.

Thanks guys.

Sub CompareRangesII()    
    Dim r As Range
    Dim Dict As Object
    Dim Sht1 As Worksheet
    
    Set Dict = CreateObject("scripting.dictionary")
    Set Sht1 = Worksheets("Test")
    
    With Dict
    .CompareMode = vbTextCompare
    For Each r In Sht1.Range("T2", Sht1.Range("T" & Rows.Count).End(xlUp))
    If Not .exists(r.Value) Then .Add r.Value, r.Row
    Next r
    'Look through list in Column H and asertain if exists in Column T
    
    For Each r In Sht1.Range("H1", Sht1.Range("H" & Rows.Count).End(xlUp))
    If Not .exists(r.Value) Then
    r.Interior.Color = RGB(184, 205, 208)
    'this highlights Column H values Not in Column T list
    r.Font.Bold = True
        End If
        Next r
        End With
    Set r = Nothing


    End Sub

 

[Fluff]

how about

Sub CompareRangesII()
   Dim r As Range, Rng As Range
   Dim Dict As Object
   Dim Sht1 As Worksheet
   
   Set Dict = CreateObject("scripting.dictionary")
   Set Sht1 = Worksheets("Test")
   
   With Dict
      .CompareMode = vbTextCompare
      For Each r In Sht1.Range("T2", Sht1.Range("T" & Rows.Count).End(xlUp))
         If Not .Exists(r.Value) Then .Add r.Value, r.Row
      Next r
      'Look through list in Column H and asertain if exists in Column T
      
      For Each r In Sht1.Range("H1", Sht1.Range("H" & Rows.Count).End(xlUp))
         If Not .Exists(r.Value) Then
            If Rng Is Nothing Then Set Rng = r Else Set Rng = Union(Rng, r)
            r.Interior.Color = RGB(184, 205, 208)
            'this highlights Column H values Not in Column T list
            r.Font.Bold = True
         End If
      Next r
   End With
   Set r = Nothing
   If Not Rng Is Nothing Then Rng.Copy Sheets("Sheet1").Range("A1")
End Sub

 

[billandrew]

Fluff

Thank you for the quick response. Works great!

By adding an additional range Rng variable - Does this store the values I need?

not entirely sure what role the Union is playing..

 

[Fluff]

You're welcome

Union simply joins 2 or more ranges into 1 range, so that rather than copying the ranges one at a time, you can copy them all at the end.

 

[billandrew]

Thanks

I know you heard this before... How can I resize the range Column A through Column L and copy to....

 

[Fluff]

Try

Rng.Offset(, -7).Resize(, 12).Copy Sheets("Sheet1").Range("A1")

 

[billandrew]

I'm breaking at this point - unsure why

'If Not Rng Is Nothing Then Rng.Offset(, -7).Resize(, 12).Copy Sheets("Sheet4").Range("A1")

 

[Fluff]

What error do you get?

 

[billandrew]

Run time error 1004

 

[Fluff]

What's the actual message, 1004 could be a number of things.