Forum Discussion

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 mo...

Macros & VBA

Requests

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