Set up dynamic named range using vba

  • HI, this sub works but puts "" around the range in name manager. Any ideas on how to overcome this?


  • Re: Set up dynamic named range using vba

    Names don't always refer to cells, sometimes they hold a value.

    This code loops through B3:B8 and creates six new names in the name manager, the names are what's in those cells and what they refer to is not a range but the values in the cells in the C column on the same row.

    If after you run the code you delete the values in the B column and the C column it has no effect on the names or what they contain.

    Let's say B3 has the text "Test" in it and C3 has the value "Run" in it, then after running the code we could type =Test into a blank cell and the result would be Run in the cell after we hit enter.

    Names can even be used to create formulas.

    What is it you want to do?

    Bruce :cool:

  • Re: Set up dynamic named range using vba

    Hi thanks for the reply. Just to clarify, I understand the code, I wrote it.
    i have 400 named ranges to set up in a workbook and this is to automate the task to save hours of manual work. B has a named range and C has the text of a formula that creates a dynamic named range. The macro copies the name and the formula to the name manager. Unfortunately when it copies the formula it puts " " around it and the name manager doesn't recognise it. I was trying to see how to copy it without " ". Thanks for trying to help though.

  • Re: Set up dynamic named range using vba

    Genius!! Thanks pal.
    i had tried rng.value and rng.text etc and knew it would be a different operator but couldn't work it out.
    Many thanks

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!