VBA to Create Named Ranges based on values in a table

[jwb1012]

Hello, I am trying to figure out if its possible to develop a macro that will create named ranges for me so I don't have to manually create them.

• In column A I have the name of each product category (996 product categories - rows 11:1006) .
• In columns T:BQ I have the name of each product within that product category (50 products within each product category)
• So, each row has the name of the product category (i.e. shoes) in column A and the 50 products that fall within that category (i.e. Sandals, running shoes, etc.) in columns T:BQ

I am looking for a macro that can create a named range for each row. The name of the range should be the product category from column A (i.e. shoes) and the range should be columns T:BQ for that row.

Does anyone have any thoughts on how this can be accomplished without having to manually do this for all 996 rows? This would be amazing! Thank you in advance for any guidance or support you may be able to provide.

 

[mikerickson]

It strikes me that named ranges are not the way to approach this.

Are the product categories unique, do they provide a unique identifier for the row?

 

[jwb1012]

I need the named ranges for data validation drop down lists. The goal of the macro is just to create the named ranges (one time - will not need to be repeated once the named ranges are developed).

 

[mikerickson]

=INDEX($T$11:$BQ$1006, MATCH("Shoes", $A$11:$A$1006, 0), 0)

will return cells T:BQ of the row where "shoes" is the entry in column A