Dynamically Create Named Ranges Based On Cell

    Hello all


    I am looking for a solution to dynamically create named ranges according to the contents of cells in a particular column.


    The following code works for 1 word names, but in many cases the title cell contains numerous words separated by spaces. Is there a way I can adapt this code so that it will name the ranges with the spaces removed? For example, where cell C2 contains the narrative 'Sales Ledger Control', I would want the range name 'SalesLedgerControl'.


    Code
    Range("R2:Z2").Name = Range("C2")


    The named ranges are referred to in numerous other worksheets, where selecting a particular narrative from a listbox creates a dependant drop-down in the adjacent cell (eg if Sales Ledger Control is selected, the dependant list contains names of customers).


    FYI, the data validation in the other worksheets ignores the spaces in the range names, ie:


    =INDIRECT(SUBSTITUTE(G2," ",""))



    It would not take me long to name the ranges manually, but a macro is preferable because the narratives in the title cells will often change and the range names will obvioulsy also therefore change.


    Any help would be appreciated as always.

    Re: Name Ranges Dynamically


    untested:


    Code
    Range("R2:Z2").Name = application.worksheetfunction.substitute(Range("C2")," ","")


    p45cal

    Re: Name Ranges Dynamically


    P45cal, many thanks. Tried and tested and works perfectly.


    To trouble you again, do you know of a way that I can implement this code for every line within the worksheet, without having to enter the code again for each specific row?


    Ideally, I would like code that will create a new range name whenever any individual narrative in column C is changed (currently approx 120 rows but will ultimately be more).


    Hope this makese sense! Thanks again.

    Re: Name Ranges Dynamically


    In the sheet's code module:


    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 And Target.Column = 3 Then
  Target.Offset(, 15).Resize(, 9).Name = Application.WorksheetFunction.Substitute(Target.Value, " ", "")
End If
End Sub

    every time a single cell in column C is changed it will create a new name for the cells in columns R to Z in the same row. To create names for pre-existent rows, select the cells in column C for the rows you want to do this for and run this macro:


    Code
    Sub blah()
For Each cll In Selection.Cells
  cll.Offset(, 15).Resize(, 9).Name = Application.WorksheetFunction.Substitute(cll.Value, " ", "")
Next cll
End Sub

    No checks are made in this second macro to ensure the selected cells are only in column C. It would be an idea to run this second macro before enabling the first macro.


    p45cal

    Re: Name Ranges Dynamically


    P45cal,


    Your code works absolutely perfectly and I am very grateful indeed for your advice. Thank you my friend.

Participate now!

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

Register Yourself Login