split range address into individual cell addresses

[noname]

hi,

Given a range say, "A1:E1", i need a formula to return a string of cells "A1","B1","C1","D1","E1". I do not want to use helper columns.

has anyone tried something like this?

 

[Ron Rosenfeld]

hi,

Given a range say, "A1:E1", i need a formula to return a string of cells "A1","B1","C1","D1","E1". I do not want to use helper columns.

has anyone tried something like this?

Your question is not clear.

If you want to return a string that consists of the concatenation of the cells in your defined range, Excel does not have a built in function to do that unless you know the orientation and dimension of the string beforehand.

In your example, which is a horizontal string consisting of five elements, you could extend the following to the fifth element:

=INDEX($A$1:$E$1,1,1)&" "&INDEX($A$1:$E$1,1,2) & " " & INDEX($A$1:$E$1,1,3) ...

and there are other formulas that could work similarly, but would have the same limitations.

If a VBA solution is allowed, it is relatively simple.

Here is a User Defined Function that allows you to supply a range of any size, and includes an optional argument for a separator. If the argument is omitted, the routine will insert a <space> between each element.

To enter this User Defined Function (UDF), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=MultiCellConcat(A1:E1)

in some cell.

===========================================
Option Explicit
Function MultiCellConcat(rg As Range, Optional Sep As String = " ") As String
Dim s As String
Dim c As Range
For Each c In rg
s = s & Sep & c
Next c
MultiCellConcat = Mid(s, 2)
End Function
====================================================

 

[noname]

hi,



Given a range say, "A1:E1", i need a formula to return a string of cells "A1","B1","C1","D1","E1". I do not want to use helper columns.



has anyone tried something like this?

Hi Ron,

thanks for your reply.
my question is very clear and i.e. to split a range address into its individual cell addresses (cell references). the above formulae containing index functions only returns the VALUE at those indexes and not their ADDRESSES. Also, it means, if you have 30 cells in a range, it would involve writing 30 INDEX functions and concatenating them together. Will appear too long!

your VBA solution takes into account a FOR EACH LOOP, which i would like toavoid. i need a shorter code aka a one- liner!

 

[noname]

Hi Ron,



thanks for your reply.

my question is very clear and i.e. to split a range address into its individual cell addresses (cell references). the above formulae containing index functions only returns the VALUE at those indexes and not their ADDRESSES.. Also, it means, if you have 30 cells in a range, it would involve writing30 INDEX functions and concatenating them together. Will appear too long!



your VBA solution takes into account a FOR EACH LOOP, which i would like to avoid. i need a shorter code aka a one- liner!

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 multiplecells.

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

 

[Ron Rosenfeld]

Hi Ron,

thanks for your reply.
my question is very clear and i.e. to split a range address into its individual cell addresses (cell references). the above formulae containing index functions only returns the VALUE at those indexes and not their ADDRESSES. Also, it means, if you have 30 cells in a range, it would involve writing 30 INDEX functions and concatenating them together. Will appear too long!

your VBA solution takes into account a FOR EACH LOOP, which i would like to avoid. i need a shorter code aka a one- liner!

Your question may have been clear to you, but it is open to a variety of interpretations.

A "string of cells" does not convey the same meaning to me as would the phrase "A string of cell addresses"; and, given your example, it seems that what you really want is:
Given a range reference, return a string of cell addresses delimited by quotation marks and separated by commas.
It is also not clear (to me) if the "range" is being entered as a string or as a reference.

Furthermore, your requirement for a one-liner and "shorter code" suggests there is something else going on than mere production of the string. Care to share that?

In any event, taking into account that the range reference may be passes as either a string or as a range, the following UDF should return the result you show in your original question. It does require a loop and does some rudimentary error checking.

=============================
Option Explicit
Function RangeAddresses(ByVal rg) As String
Dim r As Range, c As Range
Dim v() As Variant, i As Long

On Error GoTo Handler
Select Case VarType(rg)
Case Is = vbArray + vbVariant
Set r = rg
Case Is = vbString
Set r = Range(rg)
Case Is = vbEmpty
Set r = rg
End Select
On Error GoTo 0

ReDim v(0 To r.Count - 1)
For Each c In r
v(i) = c.Address
i = i + 1
Next c
RangeAddresses = """" & Join(v, """,""") & """"
Exit Function

Handler:
MsgBox ("Invalid Range Reference")
End Function
================================

 

[Ron Rosenfeld]

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 an equivalent single-line code in VBA? as far as i know, the worksheet CELL function does not have any equivalent in VBA.

Are you now changing your specifications?
Intially, you described a quote-delimited, comma separated string. But this formula of yours only returns one address per cell, and only in a range the same vector and size as your original range reference.

I think your description of what you want is not as clear to me as it seems to be to you.

Perhaps you could rethink how you want to express your requirements; and maybe some more information on the overall project might be useful in devising an efficient solution.

 

[noname]

Are you now changing your specifications?

Intially, you described a quote-delimited, comma separated string. But this formula of yours only returns one address per cell, and only in a rangethe same vector and size as your original range reference.



I think your description of what you want is not as clear to me as it seems to be to you.



Perhaps you could rethink how you want to express your requirements; and maybe some more information on the overall project might be useful in devising an efficient solution.

you are right. i need the one-liner code for something else. see attached file.

http://sdrv.ms/NsOdAV

when we use form Groupboxes & OptionButtons on a worksheet, to select thoseOptionButtons to hide/unhide them, we have to loop through all the optionbuttons in a worksheet i.e. Activesheet.optionbuttons.visible=true/false.

I have named my optionbuttons as "opt_" & cells(i,j).topleftcell.address. idon't want to hide/unhide all the optionbuttons in a worksheet, but the ones found in chosen Region rows. i have used checkboxes to tick/untick Region rows. Now, looping thru all the activesheet.optionbuttons and matching the correct optionbutton name with the topleftcell, takes time. Also, there is some major problem with hiding/unhiding the optionbuttons, ie. they donothide with the rows but shift down or up.

 

[Ron Rosenfeld]

you are right. i need the one-liner code for something else. see attached file.

http://sdrv.ms/NsOdAV

when we use form Groupboxes & OptionButtons on a worksheet, to select those OptionButtons to hide/unhide them, we have to loop through all the optionbuttons in a worksheet i.e. Activesheet.optionbuttons.visible=true/false.

I have named my optionbuttons as "opt_" & cells(i,j).topleftcell.address. i don't want to hide/unhide all the optionbuttons in a worksheet, but the ones found in chosen Region rows. i have used checkboxes to tick/untick Region rows. Now, looping thru all the activesheet.optionbuttons and matching the correct optionbutton name with the topleftcell, takes time. Also, there is some major problem with hiding/unhiding the optionbuttons, ie. they donot hide with the rows but shift down or up.

Complicated code.

But I still don't see a way of doing it with a one-liner in VBA.
However, the fact that you have control over the input simplifies the code considerably, but I still think you'll have to loop through the address range in order to get them out. Especially since the individual cell addresses are not a property of the range object. You could put the addresses into an array, and then use them to find your controls, I suppose. You'll need someone more knowledgeable than I to help further, though.

 

[noname]

Complicated code.



But I still don't see a way of doing it with a one-liner in VBA.

However, the fact that you have control over the input simplifies the code considerably, but I still think you'll have to loop through the address range in order to get them out. Especially since the individual cell addresses are not a property of the range object. You could put the addresses into an array, and then use them to find your controls, I suppose. You'll need someone more knowledgeable than I to help further, though.

Yes, thats what i have in mind, ie. once i have the split addresses in a string e.g StrAddress, then something like,
with Activesheet
.optionbuttons(Array(StrAddress)).visible=false/true.
end with

 

[Ron Rosenfeld]

Yes, thats what i have in mind, ie. once i have the split addresses in a string e.g StrAddress, then something like,
with Activesheet
.optionbuttons(Array(StrAddress)).visible=false/true.
end with

It's easy to set up the array, especially if you can control your input type:

========================================
Option Explicit
Sub ClrRange()
Dim r As Range, c As Range
Dim s() As String 'or possibly as range depending on the required
'argument type for your option buttons property
Dim i As Long
Set r = Application.InputBox("Clear Range: ", Type:=8)
ReDim v(0 To r.Count - 1)
For Each c In r
s(i) = c.Address
i = i + 1
Next c

'With ActiveSheet
' .optionbuttons(s).visible=false/true.
'End With

End Sub
================================