Concatenate with several criteria and looping

if columns B:G have values then concatenate columns (b," ","and",e," ", G) in cell __

Columns B & G or B thru G? What are you trying concatenate? and which cell is it supposed to go into?

Correct B through G and I want the output to go to column A

I am trying to concatenate names. There is a column for husband and wife prefix, first name, and last name each.
Depending on what is present I need to concatenate those columns in a specific order as shown in my example.

I hope that clarifies
Thanks

Originally Posted by XeRo Solus

Columns B & G or B thru G? What are you trying concatenate? and which cell is it supposed to go into?

To attach a Workbook
(please do not post pictures of worksheets)

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Begins to loop through all rows

Please Login or Register  to view this content.

loops through B:G in a row

Please Login or Register  to view this content.

Counts each cell in B:G that is not empty

Please Login or Register  to view this content.

If none are empty in concatenates these values.

Please Login or Register  to view this content.

Else if some are empty it concatenates these cells.

However, if you're going to add criteria you should probably use Select Case