redefine/resize named range VBA

[vizzkid]

Hi,

How can i redefine a named range in VBA?

I've attached a example workbook: I'm trying to define my named range "prices" based on ticked/non-ticked check boxes. If Check box 1 is ticked the range should be column A:C, check box 2 column F:H, Check box 3 column D,E,I. And of course if multiple check boxes are selected then the all of columns of each "option" should be included in the named range.

This is how far i've gotten so far

Please Login or Register  to view this content.

[Andy Pope]

Please Login or Register  to view this content.

Note how ever your formula will not work if only checkbox 3 is checked due to the non contiguous range.

[vizzkid]

Thanks a lot for your code! Is there no way to make it work with non-contiguous ranges? Just had an idea of copying the "wanted" prices to a new sheet (contiguous or not) and then have dynamic named range (ie using offset()) is that new sheet.

Do you think that would work? Or is there no way of copying non-contiguous ranges so that they are contiguous (in a new sheet)?

[Andy Pope]

The code works and the named range Prices is updated, both contiguous and non contiguous.

It is the formula you have based on prices that is not working. Try selecing option 3 only and updating.

If you copy the range Prices it will paste as a contiguous block.

[vizzkid]

Well that's kind off my original issue. Don't get me wrong, your code was VERY helpful, but i need to be able to select non-adjacent columns as my named range "prices". Is there anyway of doing this that you know? Doesn't really matter if the code gets messy or if I have to copy my data to a new data and then define "prices" based on the data in that new sheet.

[Andy Pope]

the variable rngUsePrices contains a reference to the selected range, whether it's contiguous or not.
So you can use that to copy the information to another sheet and then do your formula or what ever from there.

[vizzkid]

Hm.. It's probably just me not understanding what you mean, but i tried changing the last lines to

Please Login or Register  to view this content.

Just to see if it would print the all columns even if they were non-contiguous. That didn't work as expected, choosing "option 3" will only print the 2 adjacent columns, leaving out the third column.

Maybe I have to use a for-loop to extract all the information from rngUsePrices, as all the information SHOULD be in there..? I'm a bit confused as to how the Union() function works, as the union only seems to work if the columns are adjacent..

[Andy Pope]

Please Login or Register  to view this content.

[vizzkid]

I see..

Well this solves a lot of problems, thank you VERY MUCH (!) and thank you for being so patient