Create code to update named ranges in a table. Table contains Many Name Ranges

**Calio** · 01-08-2014, 11:50 AM

08.01.png

Named Ranges are used on validation lists(ws down on the image), these validation lists depend on the category selected. The calculation at the end depends on both: category and description(Please refer to attachment for a better understanding)

The problem is that this table is often modified and more categories are added as well as more descriptions per category.(actually I got 4 tables as this in separate ws).

Could someone help me to create a macro that update the Named Ranges?
e.g.: if a new record is entered in Category1, then the description is in the Named Range "SW_EPS_Bp_Category1". As well as if a completely new category is added, lets say "Category4", then the name for the range should be created as well.

It is hard to get it by reading it the first time, or could be that I am not explaining myself properly. So please dont hesitate to ask questions.

Thanks in advance for your help. Have a nice one!

**natefarm** · 01-08-2014, 04:17 PM

If I'm understanding your request correctly, this should give you a good start, and has some flexibility for adjustments.

Sub SetNames()
Dim BegRw As Long, EndRw As Long, RngName As String, SheetName As String
Const NameCol = 11  ' Adjust as needed
Const DescCol = "C"

    BegRw = 2
    RngName = Cells(BegRw, NameCol).Value
    SheetName = ActiveSheet.Name
    If RngName = "" Then
        MsgBox "First name not found"
        Exit Sub
    End If

    Do Until RngName = ""
        EndRw = BegRw + 1
        Do Until Cells(EndRw, NameCol).Value <> "" Or Cells(EndRw, 1).Value = ""
            EndRw = EndRw + 1
        Loop
        ActiveWorkbook.Names.Add Name:=RngName, _
            RefersTo:="=" & SheetName & "!$" & DescCol & "$" & BegRw & ":$" & DescCol & "$" & EndRw - 1
        BegRw = EndRw
        RngName = Cells(BegRw, NameCol).Value
    Loop
End Sub

**Calio** · 01-09-2014, 11:07 AM

Thanks it really gave me the way to start. Here is the end code ant the attachment.

Again, many thanks and probably, see you around!

Sub SetNames()
Dim BegRw As Long, EndRw As Long, RngName As String, SheetName As String, LastRow As Long, Range As String
Const NameCol = 19  ' Adjust as needed
Const DescCol = "D"

    Call Pre_SetNames 'Macro to add * that show the end of the range
    
    With ActiveSheet    'finds last used row
        LastRow = .Cells(.Rows.Count, "S").End(xlUp).Row
    End With
    
    BegRw = 4
    RngName = Cells(BegRw, NameCol).Value
    SheetName = ActiveSheet.Name
    
    If RngName = "" Then
        MsgBox "First name not found"
        Exit Sub
    End If

    Do Until EndRw >= LastRow
        
        If RngName = "*" Then   '"*" is a reference, the next field is the range name (RngName)
            RngName = Cells(BegRw, NameCol).Value
        End If
        
            Do Until RngName = "*" Or EndRw >= LastRow
                EndRw = BegRw + 1
                    
                    Do Until Cells(EndRw, NameCol).Value <> "" Or Cells(EndRw, 1).Value = "*"
                    EndRw = EndRw + 1
                    Loop
            
                ActiveWorkbook.Names.Add Name:=RngName, _
                     RefersTo:="='" & SheetName & "'!$" & DescCol & "$" & BegRw & ":$" & DescCol & "$" & EndRw
                BegRw = EndRw + 1
                RngName = Cells(BegRw - 1, NameCol).Value
            Loop
        
    Loop
    
End Sub


'Macro to prepare the table
Sub Pre_SetNames()

Dim Rw As Long, LastRow1 As Long
Const NameCol2 = 19
Const NameCol1 = 1

    With ActiveSheet
        LastRow1 = .Cells(.Rows.Count, "S").End(xlUp).Row
    End With

    Rw = 3
    
    Do Until Rw >= LastRow1
        Rw = Rw + 1
        If Cells(Rw, NameCol1) <> "" And Cells(Rw - 1, NameCol1) = "" Then
            Cells(Rw - 1, NameCol2) = "*"
        End If
    Loop
    
    Cells(LastRow1 + 1, NameCol1) = "*"
    Cells(LastRow1 + 1, NameCol2) = ""

End Sub

09.01.png

**Calio** · 01-09-2014, 11:08 AM

oh...looks awful, I didnt find how to add the "code box"...

**natefarm** · 01-09-2014, 11:13 AM

Select your code and click the Code Tags (#) button. Also, before posting, I always click Go Advanced, and then Preview Post. You can go to your post above and click Edit to fix it. Welcome to the forum!

Create code to update named ranges in a table. Table contains Many Name Ranges

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Create code to update named ranges in a table. Table contains Many Name Ranges

Re: Create code to update named ranges in a table. Table contains Many Name Ranges

Re: Create code to update named ranges in a table. Table contains Many Name Ranges

Re: Create code to update named ranges in a table. Table contains Many Name Ranges

Re: Create code to update named ranges in a table. Table contains Many Name Ranges

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Multiple SumIFs with named table ranges

Pivot Table Using Multiple Named Ranges

Worksheet_Change procedure & multiple named ranges: specific cells/ranges fail to update

Create a Macro to Update Named Ranges

Applying named ranges to Pivot table

Bookmarks

Bookmarks

Posting Permissions