Forum Discussion

Jasmin Nolasco's avatar
Jasmin Nolasco
Copper Contributor
Jun 12, 2018

VBA to Create Named Ranges

Hello!

 

I am trying to create named ranges in Excel using VBA, but I'm not sure how I would do this when the cell/row range varies. I want to use the module on different excel files. I want the module to create a named range under specific words such is "Calls","Full Name", etc. 

Macros & VBA
Requests

1 Reply

Sort By
  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Jun 12, 2018

    Jasmin-

     

    Hope you're doing well.  If the range varies you'll need to create variables to account for these adjustments and input them into code like this:

     

     

    Sub Test()

    Dim sht As Worksheet
    Dim lrow As Integer
    Dim MyNamedRng As Range    
    
    Set sht = Sheets("Sheet1")
    lrow = sht.Cells(Rows.Count, "A").End(xlUp).Row
    Set MyNamedRng = sht.Range("A2:A" & lrow)
    ActiveWorkbook.Names.Add _
            Name:="Calls", _
            RefersTo:=MyNamedRng
        
End Sub

    Here is some more detailed information regarding named ranges using VBA:

    https://www.thespreadsheetguru.com/blog/the-vba-guide-to-named-ranges

     

Resources