+ Reply to Thread
Results 1 to 31 of 31

Concatenate 4 columns using vba automatically

  1. #1
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Concatenate 4 columns using vba automatically

    I need a code to concatenate 4 columns 'c, d, e, f' in column 's' automatically after moving cursor in column 'g' ( by tab, arrow key or mouse).

    Please suggest me a code.

    Thanking you in anticipation.

    Mukesh
    Attached Files Attached Files

  2. #2
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Concatenate 4 columns using vba automatically

    Try this .
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    810

    Re: Concatenate 4 columns using vba automatically

    Try this:
    Please Login or Register  to view this content.
    Sorry I missed this part so my macro is not working for you
    automatically after moving cursor in column 'g' ( by tab, arrow key or mouse).
    Updated: Try the sample in attached and double click on column G

    Regards,
    tt3
    Attached Files Attached Files
    Last edited by tuongtu3; 09-24-2013 at 09:57 PM.

  4. #4
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Concatenate 4 columns using vba automatically

    Good morning millz,
    Excellent! It's working fine as per my requirement.

    Thank you for solving my problem quickly. Have a nice day.

    Mukesh

  5. #5
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Concatenate 4 columns using vba automatically

    Hi tt3,
    Thank you for solution. It's also working fine but only I have to manually run the macro each time. MillZ's macro is working automatically after moving in column 'g' as per my requirement.

    Don't mind. I'm very much thankful to both of you for solving my problem.

    Thank you and have a nice day.

    Mukesh

  6. #6
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Concatenate 4 columns using vba automatically

    Sorry Millz,
    I want to concatenate the 4 columns 'c, d, e, f' in column 'g'. I modified the code but something is wrong so I request you to guide me again. After typing the middle name in column 'f' I moved the cursor to column 'g' at this time the column 'g' should have a concatenate output. Guide me please!

    Thank you and sorry for trouble.

    Mukesh
    Attached Files Attached Files

  7. #7
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Concatenate 4 columns using vba automatically

    Change to:
    Please Login or Register  to view this content.

  8. #8
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Concatenate 4 columns using vba automatically

    Dear Millz,
    Good morning! I'm facing a problem in this code as follows:

    When I move my cursor from column 'f' to column 'g' the concatenate output is display in column 'g' automatically. It creates a problem for me in table titles row 7. It also change to these four column concatenate output which is not required.

    The column 'g' is of address. I want the name of person in address column on first line and the address will start from second line. But now if I type address in second line and then while reading the table I move the cursor from column 'f' to column 'g' the address is deleted and concatenate output is display. I don't want to delete the address. To avoide this I want to modify the code as follows:

    When I type any letter or dash (any value) in column 'f' then only the column 'g' should change. So that the address will not be deleted. I also want to change the sequence of concatenate output as 'Title first name, middle name, surname' for address in column 'g'.

    In this way I want to avoide typing the name of person and save time.

    Sorry for trouble. Thank you.

    Mukesh
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    810

    Re: Concatenate 4 columns using vba automatically

    Not sure if this work:

    Please Login or Register  to view this content.
    Regards,
    tt3

  10. #10
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Concatenate 4 columns using vba automatically

    Thank you. It's partially working fine. It solved the title problem but repeating the concatenate output in column 'g' several times I move the cursor from column 'f' to column 'g'. For this the condition should be:

    If type any value in column 'f' then only the concatenate output should be display in column 'g' and not after moving from column 'f' to column 'g'.

    This will solve the problem is my guess.

    Please modify it as per this condition.

    Thank you.

    Mukesh
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    810

    Re: Concatenate 4 columns using vba automatically

    Please try the sample in attached. It's not perfect (cheating) but at least it work

    Regards,
    tt3
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Concatenate 4 columns using vba automatically

    Hi TT3,
    Good! Now the concatenate reputation problem is solved. Only one problem is there that is after moving from column 'f' to column 'g' the concatenate output is display. It deletes the address in column 'g' which I don't want. See the file attached. To avoide the amendment should be done as follows:

    If the column 'f' has any value typed then only the output of concatenate should display and not after moving cursor from column 'f' to column 'g'. I think this will solve our problem.

    Second option may be as:
    The concatenation should be restrict only for once after moving from column 'f' to column 'g' and not after that till I delete the value in column 'f' and type any new value again and move cursor from column 'f' to column 'g' again.

    Think which option is more appropriate.

    Thank you.

    Mukesh
    Attached Files Attached Files

  13. #13
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    810

    Re: Concatenate 4 columns using vba automatically

    Try to understand your question:
    Will Address in column G be entered first by user then in order C, D, E & F?
    If Address was enter first then output will be in below:
    Please Login or Register  to view this content.

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Concatenate 4 columns using vba automatically

    Isn't it simply
    Please Login or Register  to view this content.
    ?

  15. #15
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Concatenate 4 columns using vba automatically

    Hi TT3,
    No, I don't want this output. It's a address label which we write/print on the envelope. The address format should be as follows:

    Mr. Mukesh L. Baviskar
    Green valley,
    ELM Street,
    Mumbai 12344321

    The data entry sequence is as follows:

    Title, Surname, First name, Middle name And after the output of concatenate display I will go in the edit mode of that particular cell in column 'g' and start a second line of address. In this way I will save my time to type the name of person in column 'g'. It's my requirement. I don't want to change the format of address of the envelope nor the data entry sequence of entries. Now don't change the output sequence you have modified as column 'c, e, f, d' that is title, first name, middle name, surname. I also don't want to delete the address if I moved the cursor from column 'f' to column 'g' again.

    I think now my views will be clear to you to do the amendment in the code.

    Thank you.

    Mukesh

  16. #16
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Concatenate 4 columns using vba automatically

    Hello Jindon,
    Thank you for solution. Please read my reply to TT3 explaining my requirement. I want the output in column 'g' address.


    Please modify the code as per my requirement.

    Thank you.

    Mukesh

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Concatenate 4 columns using vba automatically

    Not sure if this is what you wanted
    Please Login or Register  to view this content.

  18. #18
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Concatenate 4 columns using vba automatically

    Hello Jindon,
    This code is not working. Please see the attached file.

    Thank you.

    Mukesh
    Attached Files Attached Files

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Concatenate 4 columns using vba automatically

    It is working for me as I programmed.

    It will add the name on top of whatever you enter in col.G when you change it.

    If it is not what you wanted, I don't understand what you want.

  20. #20
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    810

    Re: Concatenate 4 columns using vba automatically

    Try below two options and see if which option meet your need
    Option1
    Please Login or Register  to view this content.
    Option2
    Please Login or Register  to view this content.
    Regards,
    tt3
    Last edited by tuongtu3; 09-28-2013 at 02:07 AM.

  21. #21
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Concatenate 4 columns using vba automatically

    Hello Jindon,
    Excellent! It's working fine. I didn't understand the program working so this confusion is with me. Sorry for that.

    This is the excellent program prepared by you which fulfill my requirement. I'm satisfied with this code.

    I thank you for your excellent help and guidance to solve my problem.

    Mukesh

  22. #22
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Concatenate 4 columns using vba automatically

    Hi TT3,
    Excellent! Now these 2 options are working fine as per my requirement.

    Thank you for taking trouble to solve my problem and your guidance.

    Mukesh

  23. #23
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Concatenate 4 columns using vba automatically

    Hello Jindon,
    Sorry for trouble. Now I'm facing a problem while changing the heading of column 'g' row 7. If I delete the heading 'address' and type new heading for example 'phone' then the concatenate output is display in cell 'g7' which is wrong. How to avoide it?

    Secondly, if I want the output of concatenate in column 'r' then how to change the code?

    Sorry once again because I'm asking you after marking the thread solved but due to problem I'm helpless. Please understand me.

    Thank you.

    Mukesh
    Attached Files Attached Files

  24. #24
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Concatenate 4 columns using vba automatically

    Please Login or Register  to view this content.

  25. #25
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Concatenate 4 columns using vba automatically

    Dear Jindon,
    Thank you. The heading problem is solved.

    What change should I do to get the output of concatenate in column 'r'.

    Thanking you in anticipation.

    Mukesh

  26. #26
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Concatenate 4 columns using vba automatically

    What change?

  27. #27
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    810

    Re: Concatenate 4 columns using vba automatically

    Modified Jindon's code to get the output of concatenate in column 'r':

    Please Login or Register  to view this content.

    @Jindon: I tried to understand & learn your code
    Last edited by tuongtu3; 09-28-2013 at 10:33 PM.

  28. #28
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Concatenate 4 columns using vba automatically

    Dear tuongtu3,
    Thank you. Now the problem is solved. Now I understand the code and able to modify it as per my requirement in future.

    Thank you to both of you for guidance.

    Mukesh

  29. #29
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Concatenate 4 columns using vba automatically

    One more problem friends. If the address is not available then I enter dash sign in that cell. At this point the concatenate output displays which is not required. It looks bad. To avoide this I require a amendment in code as follows:

    if column 'r' has a dash sign enter then no concatenate output should be display.

    Please modify the code.

    Thank you.

    Mukesh

  30. #30
    Forum Contributor
    Join Date
    08-14-2012
    Location
    USA
    MS-Off Ver
    Excel 2007, MS 365 (Windows 10 Pro 64-bit)
    Posts
    810

    Re: Concatenate 4 columns using vba automatically

    Try this:

    Please Login or Register  to view this content.

  31. #31
    Forum Contributor
    Join Date
    12-15-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    672

    Re: Concatenate 4 columns using vba automatically

    Hi TT3,
    Thank you. The dash sign problem is solved now.

    Thank you.

    Mukesh

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Concatenate two columns
    By skyping in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-08-2010, 06:51 AM
  2. Concatenate columns
    By jschiefer in forum Excel General
    Replies: 14
    Last Post: 06-28-2010, 10:12 PM
  3. How to concatenate columns
    By ajkrishna001 in forum Excel General
    Replies: 2
    Last Post: 09-20-2006, 11:57 PM
  4. Concatenate two columns?
    By uma in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-19-2006, 04:50 AM
  5. Concatenate 3 columns.
    By Jim15 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-23-2006, 01:08 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1