convert all the information from a part of a column to one cell with macro

Hi all,


I want to convert all the information from a part of a column to one cell.

I have attached an excel file with an example.

All the comments that are behind the name in sheet 2, I want to convert in column E.
The problem is that in sheet 2 part of the cells are merged, and that there are multiple comments for most names.
A second problem is that the existing comments in column E must stay.

The way the data has to be shown in column E is, how you can see in column H
Is has to be done with macro, because after this I need to do different things with it.

Hope someone can help me.

keis386,

Detach/open workbook ConvertComments w2 to wT to w1 - keis386 - EF832966 - SDG15.xlsm and run the ConvertComments macro.


If you want to use the macro on another workbook:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ConvertComments macro.

keis386,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the ConvertCommentsV2 macro.

keis386,

You are very welcome. Glad we could help.

Thanks for the feedback.

Come back anytime.

Eh ... if there is nothing to copy, what is being copied that you don't want copied?

Also, which code are you using? Are you using the code provided by stanleydgromjr or myself? If you are using Stanley's code, then you need to address your question to him/her. In reviewing my code, it appears to me that if it didn't find any comments, nothing else was done.