CODEWITHSUNDEEP
excelvba
PKen
PKen

Reputation: 111

Determine if a Named Range exists, if so copy it, if not move on

I am trying to determine if a Named Range exists in my spreadsheet. If it doesn't exist, I do not want to set a variable to it. If it does exist, I want to set a variable to it.

I have tried the following, but I can't seem to get the wording quite right. 

If RangeExist("Table1") <> 0 Then
    Set QuBuild = ThisWorkbook.Worksheets("Features").Range("Table1")
Else
End If

If the named range "Table1" exists, I want to set it to the variable "QuBuild" to do something with later on (paste the table into word). If it doesn't exist, I don't want to set "QuBuild"equal to anything.

Any help would be much appreciated!!

Upvotes: 0

Views: 1260

Answers (2)

Error 1004
Error 1004

Reputation: 8220

Try:

Option Explicit

Sub test()

    Dim tbl As ListObject
    Dim ws As Worksheet

    'Loop all worksheets
    For Each ws In ThisWorkbook.Worksheets
        'Loop all tables - Listobjects
        For Each tbl In ws.ListObjects

            If tbl.Name = "Table1" Then

            End If

        Next tbl

    Next ws

End Sub

OR:

Sub test()

    Dim strNameRange
    Dim ws As Worksheets

    For Each strNameRange In ThisWorkbook.Names

        If strNameRange.Name = "Table1" Then

        End If

    Next strNameRange

End Sub

Upvotes: 0

Ryan Wildry
Ryan Wildry

Reputation: 5677

Here is an answer for Named Ranges specifically.

Public Sub ExampleUsage()
    Debug.Print DoesNameRangeExist("Table1")
End Sub

Public Function DoesNameRangeExist(ByVal NameOfRange As String) As Boolean
    Dim NamedRanges    As Names
    Dim Name           As Name

    Set NamedRanges = ThisWorkbook.Names
    DoesNameRangeExist = False

    For Each Name In NamedRanges
        If Name.Name = NameOfRange Then
            DoesNameRangeExist = True
            Exit For
        End If
    Next

End Function

Upvotes: 2

Related Questions