Help request to concatenate the columns in require format

[mso3]

Hi friends,

There are 5 columns from 'S:W' namely
City, Block, District, State, Country. I want to concatenate it in column 'X' as follows:

1 line: City, 'Value from "Support" sheet range U6' Block,
2 line: 'Value from "Support" sheet range U7', District, State
3 line: Country

in a single cell. The values from "Support" sheet are in local language which can not be written in vba window so I have retrieve it from "Support" sheet.

Any help will be highly appreciated.

Thanking you in anticipation.

[mso3]

Sorry! The code is as follows:
I'm not getting require output.

Please Login or Register  to view this content.

Thank you.

[Mumps1]

Try this macro in a regular module and run it from there. Before you run the macro, remove the hyphens ( - ) in columns S:U so that those cells are blank. Also, remove the change event macro in the worksheet code module.

Please Login or Register  to view this content.

[mso3]

Thank you.

No, it's not as per my requirement. If there is no data range 'S:W' then the user put a dash sign as shown in attached sample workbook.
I want to replace those dash sign to "" in column 'X'
The values 'Bl.' and 'Dis.' are store in "Support" sheet which are not in English so I place it there. These are inn local language which cannot be written in vba code window. I want to retrieve it from there only. The column titles are also in local language in my original workbook so I used the sheet event code which didn't require the column title in code.
I need a sheet event code to get the output automatically after entering the value in range 'S:W'.
Could you please suggest me an amendment in the code posted in post2 or similar sheet event code?

Thanking you.

[Mumps1]

Give this a try:

Please Login or Register  to view this content.

[mso3]

Hello Mumps1,

Excellent! Perfect, now it's working fine as per requirement.
I appreciate and thank you for the excellent challenging solution.

Have a nice day.

[Mumps1]

My pleasure.