Dynamic Named Ranges using other cell values

Div_1

Please Login or Register  to view this content.

div_2

Please Login or Register  to view this content.

div_3

Please Login or Register  to view this content.

the forum lower - cased this, not me. will still work though

Neil,

Thank you for offering this solution.

I should have been a bit more specific however, as Col G can extend for thousands of rows, and there could be hundreds if not thousands of DIV values as well.

Per the solution you've provided, it appears that my macro would need to contain every possible one of those contingencies, if I am interpreting the usage of offset(g2, match([x],...) correctly -- where [x] represents the Div numbers?

If so, I would be really interested in knowing instead if there is a way to automatically leverage whatever values exists in Col E when naming the range, so rather than having separate formulas that have pre-defined values for DIV, just a general way of being able to detect the values in Col E and using those as part of my named range.

Thoughts?

Use a scripting dictionary to get the unique Divs. Loop through the dictionary and use .Find and Union() to define the ranges. Code is on its way...

Please Login or Register  to view this content.

Edit: added an offset to get the right column.

Column E doesn't have to be sorted for this to work by the way, but it may throw an error if the named ranges get too complex.

You're most welcome!