VBA Named Ranges

**tschuman** · 07-05-2013, 02:51 PM

I'm new to VBA coding so please bare with me.

The problem I'm having is that I have dynamic named ranges within excel and I need to reference them later in the excel document by using the indirect function. I'm aware of the problem that the indirect funciton does not work with dynamic named ranges, so I'm trying to create a workaround.

I'm looking for a VBA code for a macro that would begin by deleting any named ranges, and then create named ranges for information in column B-F and K-M (rows 3 to the final row with information in that column) based on the column header that is in the second row (ie B2).

Hopefully this will work and I can then use the indirect funciton to reference the created named ranges and just re-run the macro every time the named ranges need to include more/less information.

**shg** · 07-05-2013, 02:53 PM

Why do you need to use INDIRECT, which is volatile?

**tschuman** · 07-05-2013, 03:22 PM

Im looking at how certain things coorelate with sales. And then looking how combining certain things can coorelate with sales.
The information in columns A-L will vary depending on the information being looked at. This then changes the length of the named range. These are then referenced using a vlookup. I would use a dynamic named range, but when trying to combine them later to see how a hybrid would correlate with sales i need to be using an indirect function (which doesn't work with dynamic ranges). If i could use a macro that would delete previous named ranges and then create new ones, I would then be able to run the macro whenever new information is used and could still use the indirect function.

**shg** · 07-05-2013, 03:25 PM

I still don't see the need for INDIRECT. VLOOKUP is certainly happy with a dynamic range.

Are you entering the name of a dynamic range in a cell?

**tschuman** · 07-05-2013, 03:27 PM

yeah i have all the named ranges listed in cells which are then referenced with the indirect function

**shg** · 07-05-2013, 03:51 PM

You could instead use a construct like

choose(somevalue, range1, range, range3)

... or probably any of several other methods.

If you think I'm being less than cooperative about doing what you asked, you'd be right. It's a bad approach, IMO, and performance will be lousy if the ranges are large, because INDIRECT is volatile and the formulas will recalculate with any change.

**tschuman** · 07-08-2013, 10:56 AM

The ranges will end up being a max of 60 cells. And while indirect does need to recalculate with any change, this is fine with me. My main question is the creation of a named range. http://www.excelforum.com/excel-prog...html?p=3313214