Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Add unique values in one Col to bottom of another

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Tasmania
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 )


    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


    HTH


    _________________
    [b] Mark O'Brien

    [ This Message was edited by: Mark O'Brien on 2002-05-06 19:16 ]

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Tasmania
    Posts
    58
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •