+ Reply to Thread
Results 1 to 17 of 17

VBA Combine Multiple Ranges Into a Single Column

  1. #1
    Registered User
    Join Date
    08-15-2015
    Location
    Minnesota
    MS-Off Ver
    office 2013
    Posts
    3

    VBA Combine Multiple Ranges Into a Single Column

    Example:
    Col A: 1,2,3
    Col B: A,B,C
    Col C: 9,8,7
    Col D: X, Y, Z
    Results
    Col E: 1,2,3,A,B,C,9,8,7,X,Y,Z

    Im completely new to VBA, as i've just converted my sheet from google sheets. I used to just be able to do ={A1:A3;B1:B3;C1:C3;D1:D3}, but there is no functionality like this in excel 2013 AFAIK.

    The function would also have to handle a variable amount of parameters.

    How do I get started!

  2. #2
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,774

    Re: VBA Combine Multiple Ranges Into a Single Column

    Try this udf function
    Please Login or Register  to view this content.
    To use it :
    Please Login or Register  to view this content.
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  3. #3
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    7,998

    Re: VBA Combine Multiple Ranges Into a Single Column

    Hi, ckthejester,

    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    To run the Excel VBA code:
    1. Press Alt-F8 to open the macro list
    2. Select a macro in the list
    3. Click the Run button
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  4. #4
    Registered User
    Join Date
    08-15-2015
    Location
    Minnesota
    MS-Off Ver
    office 2013
    Posts
    3

    Re: VBA Combine Multiple Ranges Into a Single Column

    @YasserKhalil

    This Function Concatenates the sections into a single cell. im looking for an array so that E1:E12 represent A1:D3. Also it needs to be able to take multiple parameters because the ranges i would like to combine might not be selectable in 1 single range. i.e. if i wanted only A1:A3 and C1:C3 but not B1:B3.
    Last edited by ckthejester; 08-15-2015 at 04:58 PM.

  5. #5
    Registered User
    Join Date
    08-15-2015
    Location
    Minnesota
    MS-Off Ver
    office 2013
    Posts
    3

    Re: VBA Combine Multiple Ranges Into a Single Column

    @HaHoBe

    It needs to be a function so that when the referenced cells are modified the resulting col is also updated.

    It also needs to handle multiple ranges as an input this only handles 1 input. incase the selected ranges are not adjacent to one another.

  6. #6
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,774

    Re: VBA Combine Multiple Ranges Into a Single Column

    This code may help someone to create UDF function
    Please Login or Register  to view this content.

  7. #7
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    2013 on Win10 (desktop), 2007 on Win10 (notebook)
    Posts
    7,998

    Re: VBA Combine Multiple Ranges Into a Single Column

    Hi, ckthejester,

    you would need to make clear what you understand by
    The function would also have to handle a variable amount of parameters.
    I disagree on
    It needs to be a function so that when the referenced cells are modified the resulting col is also updated.
    as that might be handled by the Worksheet_Change-event in case of any direct alteration or Worksheet_Calculate-event in case of any formula being involved by code depending on how many areas you would like to monitor.

    @YasserKhalil:
    you should implement a possibility for determining individual ranges (both number of ranges as well as ranges themself opposed to hardcoding the areas). Please keep in mind that the normal way for a For Each.. is row-wise first and thgen down, please change the hardcoded range to something like
    Please Login or Register  to view this content.
    From what I understand the resuklt should show Colum,n A values first and then Column B values and not mix them.


    Ciao,
    Holger
    Last edited by HaHoBe; 08-16-2015 at 02:43 AM. Reason: additional information

  8. #8
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,774

    Re: VBA Combine Multiple Ranges Into a Single Column

    Is there a problem in posting in the forum?

  9. #9
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,774

    Re: VBA Combine Multiple Ranges Into a Single Column

    Waiting for any updates for the UDF function to get the results in single column and to deal with multiple ranges

  10. #10
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,702

    Re: VBA Combine Multiple Ranges Into a Single Column

    Hi,

    UDF :
    Please Login or Register  to view this content.
    Usage :
    Select, for example E1:E30, enter this array formula, ended with Ctrl Shift Enter :
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards

  11. #11
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,774

    Re: VBA Combine Multiple Ranges Into a Single Column

    Mr. Karedog
    Really awesome and fantastic .. it's working like charm for non-contiguous ranges
    As for one range such as
    Please Login or Register  to view this content.
    The results are not exactly as needed ..
    the results should be like first post
    ---------------------------------
    Col E: 1,2,3,A,B,C,9,8,7,X,Y,Z

    not these results :
    -----------------
    Col E: 1,A,7,X,2,B,8,Y,3,C,9,Z

  12. #12
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,702

    Re: VBA Combine Multiple Ranges Into a Single Column

    It is the default behaviour of Excel itself, when looping a range using For Each, Excel loops sideway first, then advanced to next line.

    This is the modified code to work as you expected :

    Please Login or Register  to view this content.

    Regards

  13. #13
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,774

    Re: VBA Combine Multiple Ranges Into a Single Column

    Thanks a lot for this excellent and perfect UDF function.
    Just last point I promised you ..I need to skip blanks
    As for blank cells not to put the value 0 ..but to skip it
    Please don't attach the whole UDF function again, Just guide me to the part needed to be fixed to learn how do you achieve that

  14. #14
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,774

    Re: VBA Combine Multiple Ranges Into a Single Column

    As trying to solve it I replace this line
    Please Login or Register  to view this content.
    with this line
    Please Login or Register  to view this content.
    Instead of getting the value 0 I got blanks .. but this is not the desired result
    I need to get rid of the blanks

  15. #15
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,702

    Re: VBA Combine Multiple Ranges Into a Single Column

    You put the additional check in wrong place.
    It should be :

    Please Login or Register  to view this content.

  16. #16
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,774

    Re: VBA Combine Multiple Ranges Into a Single Column

    Mr. Karedog
    Thank you very much for this great and useful solution
    It is marvellous gift from my best friend
    Thanks a lot for all your wonderful contributions

  17. #17
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,702

    Re: VBA Combine Multiple Ranges Into a Single Column

    You are welcome, thanks to you too.


    Regards

+ 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. Combine 2 named-ranges for a single series line chart
    By ElmerS in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 12-02-2014, 07:21 PM
  2. Replies: 6
    Last Post: 02-19-2014, 10:25 AM
  3. Combine MANY tabs (with single column) into SINGLE tab.
    By nick1000 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-17-2013, 02:31 PM
  4. Replies: 3
    Last Post: 09-03-2013, 11:53 AM
  5. Replies: 7
    Last Post: 10-23-2012, 02:29 PM
  6. Replies: 0
    Last Post: 02-23-2006, 08:35 PM
  7. How do you combine a stacked column and single column graph?
    By Charlotte C in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 12-03-2005, 09:10 AM
  8. Replies: 9
    Last Post: 08-15-2005, 07:05 AM

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