Combining multiple cells in a column into one cell on top of one another?

[stangz71]

Hey everyone

I have been looking everywhere on how to combine multiple cells in a column so they end up in one cell on top of one another. The script or command would also need to know when there is a space in one of the cells and stop, then start all over once the next set of data is found. The amount of cells in the column will vary.

Example:

Cell A1: data1
Cell A2: data2
Cell A3: data3

Cell A4: data4

Cell A5: data5
Cell A6: data6

Output:

B1:
data1
data2
data3

B4:
data4

B5:
data5
data6

Any assistance would be appreciated, thanks.

[stanleydgromjr]

stangz71,

Welcome to the Excel Forum.


I am having a problem with the way you have displayed your raw data and its output.


To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, click on the New Post button, then scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

[watersev]

hi, stangz71, please check attachment, run code "test"

[stangz71]

Wow, that macro works perfectly watersev thanks for all your help. I will run a few more tests but I am pretty sure this is resolved.

[stangz71]

Is there a way I can I have it choose what column I am going to combine instead of it always choosing the A column?

[stangz71]

I was able to figure out how to adjust the column it chose on the macro so I will set this request as complete.