I familar with most of Chip's techniques. Not sure if I'm using the
ones you are but this should work. I'm using a sumproduct formula to
determine the 1st occurance of the items in column B. The SumProduct is
in an evalkuate statement. You could add a column to the worksheet to
perfrom the same thing I'm doing with the Evaluate method.


Sub MoveData()

Set Sourcesht = Sheets("Sheet1")
Set DestSht = Sheets("Sheet2")

FirstRow = 1 'first row of source sheet
FindData = "A"
NewRow = 1 'first row of destination sheet

With Sourcesht.Columns("A")
Set c = .Find(what:=FindData, _
LookIn:=xlValues, lookat:=xlWhole)

If Not c Is Nothing Then
FirstAddr = c.Address
Do
'check if this is 1st occurance of column B
RowCount = c.Row
ColB = c.Offset(0, 1).Value
Myformula = "SUMPRODUCT(" & _
"(" & Sourcesht.Name & "!A$" & FirstRow & _
":A" & RowCount & "=""" & FindData & """)*" & _
"(" & Sourcesht.Name & "!B$" & FirstRow & _
":B" & RowCount & "=""" & ColB & """))"

Results = Evaluate(Myformula)
If Results = 1 Then
'first occurance copy data
c.EntireRow.Copy _
Destination:=DestSht.Rows(NewRow)
NewRow = NewRow + 1
End If
Set c = .FindNext(after:=c)
Loop While Not c Is Nothing And c.Address < FirstAddr
End If
End With

End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=149705