Add unique values in one Col to bottom of another

[FredMFoley]

Can anyone please help with VBA code to add the values which are unique to Col B, to the end of Col A. ie I would like Col A to have a and c added to the end, without changing the order of the existing values.

Col A
q
w
e
r

Col B
a
c
w
e
This message was edited by FredMFoley on 2002-05-06 17:55
This message was edited by FredMFoley on 2002-05-06 18:06

 

[Nimrod]

This Macro assumes the sheet your using is Sheet2 So you may want to change that aspect of the macro

Sub AddColB2ColA()

'Find End of ColA
Do
BotOfColA = BotOfColA + 1
Loop Until Len(Trim(Worksheets("Sheet2").Cells(BotOfColA, 1).Value)) = 0


Do
RowCnt = RowCnt + 1


'Get a search value from col B
SrchVal = Worksheets("Sheet2").Cells(RowCnt, 2).Value

'See if can find search value in colA
Set Fnd = Worksheets("Sheet2").Range("A:A").Find(SrchVal, LookIn:=xlValues)
If Fnd Is Nothing Then ' add search value to col A
Worksheets("Sheet2").Cells(BotOfColA, 1).Value = SrchVal
BotOfColA = BotOfColA + 1
End If
Loop Until Len(Trim(Worksheets("Sheet2").Cells(RowCnt, 2).Value)) = 0

End Sub
This message was edited by Nimrod on 2002-05-06 19:07

 

[Mark O'Brien]

This example assumes that the data is on sheet1 and that there are no blanks in the data. (it may work with blanks, I just haven't test it with them )<pre>
Public Sub main()

Dim oCell As Range
Dim oRange As Range

With Sheets("Sheet1")
Set oRange = Range(.Range("B1"), .UsedRange.Columns("B"))


For Each oCell In oRange
If .UsedRange.Columns("A").Find(what:=oCell.Value, lookat:=xlWhole) Is Nothing Then
.Range("A65536").End(xlUp).Offset(1, 0).Value = oCell.Value
End If
Next
End With

End Sub</pre>

HTH


_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-05-06 19:16

 

[FredMFoley]

Thank you both very much.
(Mark's example seems happy with spaces in either column.)