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.

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

@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.

This code may help someone to create UDF function

Please Login or Register  to view this content.

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

Is there a problem in posting in the forum?

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

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

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

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

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