split range address into individual cell addresses

[sifar786]

hi,

Given a range say, "A1:E1", i need a formula OR a single line of code to return a string of cells "A1","B1","C1","D1","E1". I do not want to use helper columns if it is a worksheetfunction, OR loops if its a VBA code.

has anyone tried something like this?

Function SplitRange(myRng as Range) as String
.....
.....
SplitRange= .....
End Function

[westconn1]

very easy in vba, but not without loop

[sifar786]

well, i know how to do it using a loop, but i need a one-liner code without a loop OR an some worksheet array formula that returns an array of strings, which i can then use it as a single line of code into my VBA code.

[sifar786]

i worked out this worksheet Array formula:

={SUBSTITUTE(CELL("address",INDEX(A1:E1,1,COLUMN(A1:E1))),"$","")}

which gives me correct result i.e. "A1","B1","C1","D1","E1" across multiple cells.

But question is: how do you i convert this into a single-line code in VBA? as far as i know, the worksheet CELL function does not have any equivalent in VBA.