Merge cells into one

AD - Aug 27, 2010 at 08:05 AM
 Trowa - Aug 30, 2010 at 08:56 AM

Need help to merge range of cells [on selection] into the last selected cell [using - sign as a separator]. Is there a Function or a macro created that can do that.

For Example -

Input - On Selection:
A1 - This
A2 - Is
A3 - A
A4 - Good
A5 - Macro


A5 - This-Is-A-Good-Macro
Keep the values on cell A1:A4 as is.

Help would be highly appreciated.

Thank you and Best Regards


3 responses

Hi AD,

Try this:

Best regards,
Hi Towa,
Thank you for the solution, it worked but I think it always require a manual intervention but would it be possible for you to provide me a function/macro where we can have a <function key> that does this automatically on selected range, where the last cell on the selected list should be the destination of the previous cells. Offcourse including the destination at the end.

The reason I requested this function/macro is because I have a very huge list of items to be merged, and the biggest challange is that these range in not fix.

Hope my above request is understood.

Thank you,

Best Regards - AD
Hi AD,

Here is the macro:
Sub test()
ActiveCell.Value = ActiveCell.Offset(-4, 0).Value & "-" & ActiveCell.Offset(-3, 0).Value & "-" & ActiveCell.Offset(-2, 0).Value & "-" & ActiveCell.Offset(-1, 0).Value & "-" & ActiveCell.Offset.Value
End Sub

You can assign a <function key> if you like by pressing Alt+F8 and selecting options (bottom right of the window).

You will still need to select all the destination cells manually and run the macro after each one, but it goes a lot faster then the previously provided function.

The best way I found for doing this is to select all the destination cells and use ENTER to toggle between them. So select cells, run macro (using function key), hit enter, run macro, enter, macro, enter .... etc..

Unfortunately my limited VB knowledge doesn't allow me to write a code where you will only need to run the macro once after selecting all the destination cells.

Best regards,