Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    2,328
    Thanks
    0
    Thanked 1 Time in 1 Post

    Macro to merge text (Any)

    I have Excel worksheet that contains a columns with text. Sometimes there is only one cell in the row with text (say, in A2), sometimes there are two two cells (say, A3 "From the beginning", B3 "to the End"), sometimes three cells (A4 "From", B4 "the beginning", C4 "to the End"). Of course, the text is different from cell to cell. I need to write a macro that merge text in one cell (A4 "From the beginning to the End", B4 and C4 blank) and do it automatically through all column (A). Any help will be greatly appreciated.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to merge text (Any)

    What rows do you want to do this for? Starting at what row? Stopping at what row. Do you always want to stop at column C, or continue if there is something in columns past column C? If there is something in column A and C, but column B is empty, what do you want to do?
    Legare Coleman

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to merge text (Any)

    How are you going to determine which cells to merge? Will the first cell of a "sentence" always begin with an upper case letter, and the subsequent cells with something else? How about

    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td>1</td><td>I love</td><td>New York</td></table>
    Will there always be an empty cell after a "sentence"?

  4. #4
    Silver Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    2,328
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to merge text (Any)

    I want to do it in all non-empty rows: from 2 to 1275, or 1836, or whatever until the cell in column A will be empty. If there is nothing in column B, there is also nothing in column C. There is nothing in column D etc. (The worksheet was created from the text file, last column of which, converted into column A in the worksheet, contained text sometimes with hard returns to wrap it. Text in any cell can start either with capital or lowercase letter. There were too many entries to remove hard returns manually. But I need all text to be in one cell. Blank spaces at the end are not importamt.)

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to merge text (Any)

    Something like this?

    Sub MergeABC()
    Dim aRange As Range
    Dim aCell As Range

    On Error GoTo Exit_Sub

    Set aRange = Range(Range("A2"), Range("A65536").End(xlUp))
    For Each aCell In aRange
    If aCell.Offset(0, 1) <> "" Then
    aCell = aCell & " " & aCell.Offset(0, 1)
    If aCell.Offset(0, 2) <> "" Then
    aCell = aCell & " " & aCell.Offset(0, 2)
    End If
    End If
    Next

    aRange.Offset(0, 1).Clear
    aRange.Offset(0, 2).Clear

    Exit_Sub:
    Set aCell = Nothing
    Set aRange = Nothing
    End Sub

  6. #6
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro to merge text (Any)

    And what about this:

    Sub MergeABC()
    Dim aRange As Range
    Dim aCell As Range

    On Error GoTo Exit_Sub

    Set aRange = Range(Range("A2"), Range("A65536").End(xlUp))
    For Each aCell In aRange
    aCell = Trim(aCell & " " & aCell.Offset(0, 1) & " " & aCell.Offset(0, 2))
    Next

    aRange.Offset(0, 1).Clear
    aRange.Offset(0, 2).Clear

    Exit_Sub:
    Set aCell = Nothing
    Set aRange = Nothing
    End Sub

  7. #7
    Silver Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    2,328
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Macro to merge text (Any)

    Thank you very much

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to merge text (Any)

    Hi Servando,

    Yes, that's more efficient. Since the case where column B is empy while column A and C are filled should not occur, it should work correctly.

Posting Permissions

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