Loop through cells in a column and concatenate

Question

I would like to take all cells in column A:

*bla*

*someWord*

*two words*

*two & words*

*other - words*

Loop through them and concatenate as follows:

“my custom text” + “(”+“*[index1]*”,” * [index1] * ”, “*[index2]*”,”* [index2] *”, “*[index3]*”,”* [index3] *” + “)”

(and have a button that, upon click(), pastes the result in some cell, say B1)

(each [index] is repeated because first is without space, the second is with space between **).

I do it quite easily with Javascript in an HTML document, but I know nothing about VBA or excel macros.

Any help is appreciated.

Solution

try something like:

Sub generateString()

Range("A1").End(xlDown).Select
For i = 1 To ActiveCell.Row
    Range("A" & i).Select
    strString = strString & " " & Selection
Next i

MsgBox strString

End Sub

Comments

[DavidPostill]

Welcome to Super User. Unfortunately, we are not a code-writing service. Instead of simply asking for code to perform a particular task, please show us what you've tried so far (including any code you currently have) and where you're stuck so that we can help you with your specific problem. Questions that only ask for code are too broad and are likely to be put on hold or closed.

[fixer1234]

It looks like you are doing some markdown in your question, which doesn't translate when the question is formatted with code blocks. Please clarify whether what is currently showing is exactly how you intend it to look.

[Andrejs]

Thanks, it will serve as a great base to tweak later for various other scenarios

[Fazer87]

No worries. Remember xldown, xlleft, xlup and xlright will get all cells between your starting cell and the next blank.