VBA to Concatenate Variable Range

Dear All,
I have a problem with a worksheet that my company accounting system exports every month.

Attached : sample of the worksheet.

In column ( F ) , I need a macro to do the following calculations:
1- Check for the Title – if it begins with "Cost Center"
2- Check for the Title – if it begins with "Account Code"
3- Detect the Range Start just below "Account Code" , and End with the row above "Total"
4- Concatenate the string written in each row of the range with the string in "Cost Center"

The Story is:
Each Month I've this worksheet with hundreds of Cost Centers and subsidiary Account Codes, And to be able to analyze the accounts efficiently I need to concatenate both Cost Centers & Account Codes manually ( as you see coloring sample in the attached file ).
Which led to wasting many hours , and high risk of error while copying and pasting formulas.

Your help on this matter will be highly appreciated.
Best Regards.

Hi Arlette;

Thanks for your response.

The code displayed the message box, however no values were registered in the worksheet.

Try this code:-

Please Login or Register  to view this content.

You should install this code in a standard code module (by default "Module1") and remember to save your workbook as macro enables.
To call the code select SetAccountIDs from the list of macros available from Macros on the Developer tab. Look for Excel Help to enable this tab if you don't have it in your ribbon.

Dear Arlette;

Words can't help me thank you enough.

My mistake, I've copied the sheet1 to sheet2 in order to compare the results, Now after I've renamed the vba to sheet2 it worked like magic.

My deepest and sincere regards for you

Dear Tartarus;

Many thanks for your code.

it's a pleasure to know how much this amazing forum could help.