it will run a bit faster if you use the find method instead of looping

through the range:



Sub TheUnionOf2()

Dim rngFndPrd As Range

Dim sFndPrd As String

Dim c As Range

Dim LRow As Long



sFndPrd = Application.InputBox("Enter Col A Item.", _

"Col A Finder", , , , , , 2)



LRow = Cells(Rows.Count, 1).End(xlUp).Row

Set c = Range("A1:A" & LRow).Find(sFndPrd, LookIn:=xlValues)

If Not c Is Nothing Then

Set rngFndPrd = Union(c.Offset(0, 1), c.Offset(0, 2), _

c.Offset(0, 4), c.Offset(0, 5))

rngFndPrd.Copy

Range("K100").End(xlUp).Offset(1, 0).PasteSpecial _

Paste:=xlPasteAll, Transpose:=True

End If

End Sub


Regards

Claus B.


I'll give it a go. I'm using the codes on a small test data set, actual use could be around 5500+ rows.

I'm sure that will make a BIG difference.

Thanks, Claus.

Appreciate it.

Regards,
Howard