Many Thanks for the replies, both methods have certainly speeded things up!

p45cal one more question, just say I wanted to get the values of column D from Sheet2 into Column D of Sheet1 by comparing Column A how would I modify below code to make it work?







	
Sub GetValues3()Set rng1 = Range(Worksheets("Sheet1").Range("A1"), Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp))
Set rng2 = Range(Worksheets("Sheet2").Range("A1"), Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp)).Resize(, 2)
cc = Application.VLookup(rng1, rng2, 2, False)
For i = 1 To UBound(cc)
  If IsError(cc(i, 1)) Then cc(i, 1) = Empty
Next i
rng1.Offset(, 1).Value = cc
End Sub




Thanks Again



Fra