+ Reply to Thread
Results 1 to 11 of 11

Concatenate from data in rows using VBA

  1. #1
    Forum Contributor
    Join Date
    09-22-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Office 2019
    Posts
    259

    Concatenate from data in rows using VBA

    Hi

    In the attached spreadsheet I have several columns of data that I want to concatenate into a results table. I'm able to do this using a formula in each of the results cells, but it's not prefect. The data in the input cells is moved up and down the various columns to make sure there are gaps between where the data is repeated (i.e Date 1/1 13 rows between appearing the first and second time. This is variable). The gap between data is variable and can be set by dragging the data up or down the column. (Data from only one of the input tables may appear in the left or right lane).

    Here's the problem. If I drag and drop data the concatenate in the results gets corrupted and returns a "Ref". What I'd like to do is to do away with the concatenate formula and have a VBA code that works from a button, so that once the inputs have all been manually sorted, clicking the button will fill the results table.

    The number of rows and input tables is variable.

    I've looked at various ideas on-line but can't find anything that I understand enough to butcher to work for me!

    Thanks in advance

    Frankie.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,592

    Re: Concatenate from data in rows using VBA

    Try

    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,827

    Re: Concatenate from data in rows using VBA

    Try this ARRAY formula. In D4 then copied down.

    =TEXTJOIN(",",TRUE,IF(($H4:$AR4<>"")*(MOD((COLUMN($H4:$AR4)-8),4)=0),$H4:$AR4,""))
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  4. #4
    Forum Contributor
    Join Date
    09-22-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Office 2019
    Posts
    259

    Re: Concatenate from data in rows using VBA

    Hi Manicab!
    Thanks for the input. It's nearly there but only works for the top row (Row 5) and not the whole Results table. I've tried fitting the code into a loop using column A as the range of rows to count with ActiveCell.offset (1,0).Select at the end, but although the offset works (the cursor ends up in the fist blank cell in column A under the number 80) the code doesn't produce anything in the Left and Right columns other than the first row (Row 5).

    Any ideas please?

    Cheers

    Frankie

  5. #5
    Forum Contributor
    Join Date
    09-22-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Office 2019
    Posts
    259

    Re: Concatenate from data in rows using VBA

    Thanks for your response kvsrinvasamurthy (!!). While it does reproduce the data in the results table, if the data in the input table is moved about the results table "crashes" with "NAME" in the cells.
    Your input is appreciated though.
    Cheers

    Frankie

  6. #6
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,827

    Re: Concatenate from data in rows using VBA

    Ref post #5
    Is it possible to upload files status before moving data and after moving data.

  7. #7
    Valued Forum Contributor
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,592

    Re: Concatenate from data in rows using VBA

    Here is an update

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    03-10-2021
    Location
    Germany
    MS-Off Ver
    Office 365
    Posts
    4

    Re: Concatenate from data in rows using VBA

    If I have got right what you wished, open sheet buttons click button conc and
    contents of referring columns will be concatenated. Wish you col D und col E
    to make free click on the button del..
    Should you have some questions- you are welcome.
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    09-22-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Office 2019
    Posts
    259

    Re: Concatenate from data in rows using VBA

    Cracked it thanks maniacb. It was the .End(xlUp).Row) that was stopping it.
    Thanks for the update too.
    Cheers

    Frankie

  10. #10
    Forum Contributor
    Join Date
    09-22-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Office 2019
    Posts
    259

    Re: Concatenate from data in rows using VBA

    Yep! You got it too thanks ФдПщд.
    Great how the same thing can be achieved with different approaches.

    Thanks again

    Frankie

  11. #11
    Forum Contributor
    Join Date
    09-22-2013
    Location
    Sydney, Australia
    MS-Off Ver
    Office 2019
    Posts
    259

    Re: Concatenate from data in rows using VBA

    Hi ФдПщд

    FIXED IT! Seems to be down to running the code from the VB editor. Once the code was hooked up to the rest of the process and the editor was closed it all works fine thanks.

    Some weeks ago you were kind enough to respond to a question I put up with a code that sorted the issue. However, Having recently tried the code I find that it doesn't stop when the rows across the spreadsheet are empty. It appears to work correctly on the test sheet you submitted, but the code below just runs right through the x hundred rows of my sheet.

    Your code that I've added to to include a couple more items to move is
    Please Login or Register  to view this content.
    I've done a delete to ensure there is no data on the sheet outside that in the table rows B to AS.

    Any ideas why it won't stop running please?

    Cheers

    Frankie
    Last edited by Frankie_The_Flyer; 05-05-2021 at 06:11 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 11-15-2016, 10:38 AM
  2. How to Combine (Concatenate) Data from Multiple Rows into One Cell
    By syed82 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-02-2015, 04:38 AM
  3. [SOLVED] Macro Needed to Concatenate Data in separate ROWS
    By 823 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 02-25-2015, 01:20 PM
  4. [SOLVED] Macro Needed to Concatenate Data in separate ROWS
    By 823 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 02-21-2015, 11:35 AM
  5. [SOLVED] Formula concatenate data in multiple rows based on match data in a different row.
    By kidkool in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-17-2014, 11:01 AM
  6. Help with merge duplicates rows and concatenate non matching data
    By johnboy82 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-10-2012, 05:23 PM
  7. Concatenate Rows
    By karinos57 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-21-2007, 01:06 PM

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