+ Reply to Thread
Results 1 to 2 of 2

Smart way to concatenate into B1 all cells below until new info in Column A

  1. #1
    Dennis
    Guest

    Smart way to concatenate into B1 all cells below until new info in Column A

    Using 2003

    Currently, I insert the following VBA formula which is entered manually
    into B1. Note: In the case below, I am must change the formula by
    adding &" "&OFFSET(Bx,y,0) for the number of cells in Column A (from
    row 2 through row 7) restarting every time there is a new value (not
    empty) in column A.

    Hopefully, my columns line up close enough. I am entering this via
    "Google Groups"

    =B2&" "&OFFSET(B2,1,0)&" "&OFFSET(B2,2,0)&" "&OFFSET(B2,3,0)&"
    "&OFFSET(B2,4,0)&" "&OFFSET(B2,5,0)&" "&OFFSET(B2,6,0)&"
    "&OFFSET(B2,7,0)&" "&OFFSET(B2,8,0)&" "&OFFSET(B2,9,0
    *********************************************************************************************
    A B C D E

    1
    2 "Example 1" I The
    3 love time
    4 working is
    5 in VBA 2:00 PM
    6
    7 daily
    8 "Example 2"
    Stop when the data ends

    Note: like in Col B, there could be a blank cell in Col B
    Note: I use B1 as a helper row (maybe I do not need to??)

    When finished:


    1 Example 1 I love working The time is 2:00PM
    in VBA daily
    2 Example 2 Stop when the data ends


    Note: rows 1-my helper row, 3, 4, 5, 6, 7 & 9) have been
    macro-deleted
    Note: the change in data in Col A restarts the loop
    Note: once the data in Col A stops, the macro should stop

    The reason for the above, many use Excel as a note pad when they type
    in values in a
    column row after row as the cell fills up. Where if they used
    Text-wrap they could place
    the data in only one cell.

    I would like the macro to do all for the used range starting at Row ??
    (?? via Msg box) or for a selected range.

    Thanks Dennis


  2. #2
    Dennis
    Guest

    Re: Smart way to concatenate into B1 all cells below until new info in Column A

    Extra point: I was not quite clear about when the macro should stop.
    Previously, I mentioned when the data ends in column A. Well, there
    could be data still in the other columns to the right to be processed.

    What I should have said, the last row to be re-populated with the
    concatenated data is the last row in column in Col A with data, BUT the
    concatenating should continue in Col's B -> end of Cols used range and
    down in rows from last row in column in Col A with data to the end of
    the used range.

    Note: quite often the new user in Excel will copy formating all the way
    to 65536 and the "Used Range" is huge. Can I some how redefine Used
    Range as Range with data?

    Dennis



    Dennis wrote:
    > Using 2003
    >
    > Currently, I insert the following VBA formula which is entered manually
    > into B1. Note: In the case below, I am must change the formula by
    > adding &" "&OFFSET(Bx,y,0) for the number of cells in Column A (from
    > row 2 through row 7) restarting every time there is a new value (not
    > empty) in column A.
    >
    > Hopefully, my columns line up close enough. I am entering this via
    > "Google Groups"
    >
    > =B2&" "&OFFSET(B2,1,0)&" "&OFFSET(B2,2,0)&" "&OFFSET(B2,3,0)&"
    > "&OFFSET(B2,4,0)&" "&OFFSET(B2,5,0)&" "&OFFSET(B2,6,0)&"
    > "&OFFSET(B2,7,0)&" "&OFFSET(B2,8,0)&" "&OFFSET(B2,9,0
    > *********************************************************************************************
    > A B C D E
    >
    > 1
    > 2 "Example 1" I The
    > 3 love time
    > 4 working is
    > 5 in VBA 2:00 PM
    > 6
    > 7 daily
    > 8 "Example 2"
    > Stop when the data ends
    >
    > Note: like in Col B, there could be a blank cell in Col B
    > Note: I use B1 as a helper row (maybe I do not need to??)
    >
    > When finished:
    >
    >
    > 1 Example 1 I love working The time is 2:00PM
    > in VBA daily
    > 2 Example 2 Stop when the data ends
    >
    >
    > Note: rows 1-my helper row, 3, 4, 5, 6, 7 & 9) have been
    > macro-deleted
    > Note: the change in data in Col A restarts the loop
    > Note: once the data in Col A stops, the macro should stop
    >
    > The reason for the above, many use Excel as a note pad when they type
    > in values in a
    > column row after row as the cell fills up. Where if they used
    > Text-wrap they could place
    > the data in only one cell.
    >
    > I would like the macro to do all for the used range starting at Row ??
    > (?? via Msg box) or for a selected range.
    >
    > Thanks Dennis



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1