Breaking News: Grepper is joining You.com. Read the official announcement!

excel vba create named range

VasteMonde answered on January 6, 2021 Popularity 9/10 Helpfulness 4/10

answer excel vba create named range

related vba set named range

excel vba create named range

Comment

' Creates a named range
Sub CreateNamedRange(ByRef pWorkBook As Workbook, ByVal pWorkSheetName As String, _
    ByVal pRangeName As String, ByVal pAddress As String)
    Dim rRange As Range
    Set rRange = pWorkBook.Sheets(pWorkSheetName).Range(Chr(39) & pWorkSheetName _
        & Chr(39) & "!" & pAddress)
    If NamedRangeExist(pWorkBook, pRangeName) Then
        pWorkBook.Names(pRangeName).Delete
    End If
    pWorkBook.Names.Add Name:=pRangeName, RefersTo:=rRange
End Sub

' Checks if a range with same name exists
Function NamedRangeExist(ByRef pWorkBook As Workbook, pName As String) As Boolean
    Dim nName As Name
    For Each nName In pWorkBook.Names
        If nName.Name = pName Then
            NamedRangeExist = True
            Exit Function
        End If
    Next nName
End Function

Sub TestMe()
    CreateNamedRange ThisWorkbook, "Sheet1", "MyNewRange", "A1:D10"
    MsgBox Range("MyNewRange").Address
End Sub

Popularity 9/10 Helpfulness 4/10 Language vb

Source: Grepper

Tags: excel named range vba

Link to this answer
Share Copy Link

Contributed on Jan 19 2021

VasteMonde

0 Answers Avg Quality 2/10

Closely Related Answers

vba set named range

Comment

-1

' Scope.Names.Add Name:=RangeName, RefersTo:=NamedRange
'Example: 
ThisWorkbook.Names.Add Name:="myRangeName", RefersTo:=Range("A1")

Popularity 9/10 Helpfulness 2/10 Language vb

Source: powerspreadsheets.com

Tags: named name

Link to this answer
Share Copy Link

Contributed on Jan 06 2021

Innocent Ibis

0 Answers Avg Quality 2/10

excel vba create named range

Contents

More Related Answers

excel vba create named range

Closely Related Answers

vba set named range

Grepper

Documentation

Social

Legal

Contact

Oops, You will need to install Grepper and log-in to perform this action.