title: VBA Merge of Dynamic Named Ranges results into a static range i/o keeping dynamic
body: <p><strong>Context</strong> (VBA7.1 , Excel 2013 Pro):<br>
  all sheets and dynamic named ranges (DNR) are created programmatically. I want to merge some of the DNR units (case of same data over several columns) into a single DNR for grouping them all. At this stage, the unit DNRs and their merged result are all within the worksheet scope.</p>
  
  <p><strong>Issue</strong>:<br>
  The merged named range does not keep the dynamic property of all individual DNR. If I do it manually, it works of course, just to confirm the DNRs to merge are effectively dynamic.</p>
  
  <p><strong>Question</strong>:<br>
  how should I apply the named range merge so that the result range keeps as a dynamic named range too ?</p>
  
  <hr>
  
  <p><strong>Main Code</strong></p>
  
  <pre><code>Sub xx()
  ... some code goes here ...
      Dim DNRnames() As String
      Dim MergedRange As Range
      Dim currentRng As Range
      Dim rngStr As Variant
      Dim strStringToExclude() As String
  
      ' Get created DNRs on this sheet
          strStringToExclude = Split("_Desc,Headers", ",")
          DNRnames = DNRGetNames(aWS.Name, False, strStringToExclude)
  
        ' Merge DNRs into 1 
          For Each rngStr In DNRnames
            ' Set currentRng = aWS.Names(CStr(rngStr)).RefersToRange
            Set currentRng = aWS.Range(CStr(rngStr)) ' also this way keeps it static
            If Not MergedRange Is Nothing Then
              Set MergedRange = Union(MergedRange, currentRng)
            Else
              Set MergedRange = currentRng
            End If
          Next rngStr
  
        ' Add "MergedRange" to the aWS : ISSUE : the MergeRange is NOT dynamic...
        ' as it would be if I would create it in the ws by a named_range=(range1,range2,..)
          aWS.Names.Add Name:=DNRprefix &amp; "All", RefersTo:=MergedRange
  ...
  end sub
  </code></pre>
  
  <p><strong>GetDNR</strong>: return named ranges from the worksheet as a string array and exclude some selected named range that I don't want to get merged (it's a workaround, since I found "Union" but no "Substract" function in VBA)</p>
  
  <pre><code>Function DNRGetNames(sheetName As String, WbScope As Boolean, SuffixStringToExclude() As String) As String()  ' all DNR from one specific sheet (with wb scope or ws scope ?)
  ' https://stackoverflow.com/questions/12663879/adding-values-to-variable-array-vba
  ' https://stackoverflow.com/questions/4029975/excel-listing-named-range-in-a-worksheet-and-get-the-value
  
    ' kind of getter and setter
    Dim wb As Workbook
    Dim aWS As Worksheet
    Dim element As Name
    ReDim DNRArray(1 To 1) As String
  
    Set wb = ThisWorkbook
    Set aWS = wb.Sheets(sheetName)
  
    ' if SuffixStringToExclude is not defined, fill in the suffic string with a default fake data
    If Not Len(Join(SuffixStringToExclude)) &gt; 0 Then
      SuffixStringToExclude = Split("*FaKe!")
    End If
  
    ' populate a dynamic array with DNR related to aWS
    For Each element In wb.Names
      If Not ArrayIsInString(element.Name, SuffixStringToExclude) Then '
        If IsNameRefertoSheet(aWS, element) Then
          DNRArray(UBound(DNRArray)) = element.Name
          ReDim Preserve DNRArray(1 To UBound(DNRArray) + 1) As String
        End If
      End If
    Next element
  
    ' clean exit
    If UBound(DNRArray) &gt; 1 Then
      ReDim Preserve DNRArray(1 To UBound(DNRArray) - 1) As String
      DNRGetNames = DNRArray
    Else
      DNRGetNames = Empty
    End If
  
  End Function
  </code></pre>
  
  <p><strong>Returned DNR by the <code>GetDNR</code> function</strong> :
  <a href="https://i.stack.imgur.com/5VCR5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5VCR5.png" alt="enter image description here"></a></p>