Select Multiple Rows and Merge Cell Data

    Hi there,


    I have a need for the following:


    I want to be able to select a variable amount of rows (i.e. it could be 2 rows, 5 rows, 50 rows etc.) with a variable amount of data columns (i.e. could be 5 columns wide, 20 columns etc.) and be able to combine the data into 1 row, with the data from each row being separated by an alt+enter (or char(10)).


    Constraints:
    1) I do not want to show duplicate data (i.e. if b2 and b3 are the same value, I only want to show the value once.)
    2) Data could be text, numbers, dates, etc. (we can treat the values as strings)
    3) I want the results to override the first row in the selection and delete all the other rows in the selection (i.e if i select rows 1 and 2, I want the result to be in row 1, and have row 2 deleted)
    4) Needs to be able to be run from personal.xlsb (not saved within the actual workbook)


    I have attached a sample workbook to make it easier to understand what I am asking for: forum.ozgrid.com/index.php?attachment/46067/.
    In this example assume I selected rows 2 and 3. The result I am looking for is row 9 (which should actually overwrite row 2, and row 3 would get deleted.


    I am assuming there will need to be a userform created with the refedit control to select the range, then have an executable command button to run the routine...


    Any help would be greatly appreciated.


    Thanks!

    Re: Select Multiple Rows and Merge Cell Data



    This is untested freehand code, but is should get you started.

    Re: Select Multiple Rows and Merge Cell Data


    Like this?

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: Select Multiple Rows and Merge Cell Data


    Rory,


    Exactly like this! You never cease to amaze me.


    How long did this solution take? Firing up paypal.


    Thanks buddy,

    Re: Select Multiple Rows and Merge Cell Data


    A couple of minutes. :)

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login