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?

 

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

 

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

 

[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