title: VBA Excel: modify dynamic named range code
body: <p>Newbie question: I have module,  <a href="http://www.contextures.com/xlNames03.html" rel="nofollow">originally made by Roger Govier</a>. </p>
  
  <p>It uses an input cell <code>header</code> and creates a dynamic named range for the non empty cells positioned under <code>header</code>. The created named range will be labeled as the value of the header cell.</p>
  
  <pre><code>Private Sub CreateNamedRange(header As range)
     Dim wb As Workbook
     Dim WS As Worksheet
     Dim rStartCell As range
     Dim rData As range
     Dim rCol As range
     Dim lCol As Long
     Dim sSheet As String
     Dim Rowno As Long
  
     ' get table location
     Set rStartCell = header
  
     Set WS = rStartCell.Worksheet
     Set wb = WS.Parent
     sSheet = "'" &amp; WS.Name &amp; "'"
     With rStartCell
        Rowno = .row
        Set rData = .CurrentRegion
     End With
     Set rData = WS.range(rStartCell, WS.Cells(Rowno, rStartCell.Column))
  
      Set rCol = rData.Columns
      lCol = rCol.Column
      wb.Names.Add Name:=Replace(rCol.Cells(1).Value, " ", "_"), _
      RefersToR1C1:="=" &amp; sSheet &amp; "!" &amp; rCol.Cells(2).Address(ReferenceStyle:=xlR1C1) &amp; ":INDEX(C"        &amp; lCol &amp; ",LOOKUP(2,1/(C" &amp; lCol &amp; "&lt;&gt;""""),ROW(C" &amp; lCol &amp; ")))"
  
  End Sub
  </code></pre>
  
  <p>I want to modify this code so that instead of creating a named range it only returns the returns the range of the what would have been the named range.</p>
  
  <p>Example: 
  We have a header in <code>A1</code>, and data in <code>A2:A5</code>.</p>
  
  <p>Now:  If we call <code>CreateNamedRange(.range("A1"))</code>, it creates a dynamic named range for <code>A2:A5</code>.</p>
  
  <p>Goal: If we call  <code>CreateNamedRange(.range("A1"))</code> it returns <code>.range("A2:A5")</code> to a variable in the VBA code:</p>
  
  <pre><code>dim myRange As Range
  set myRange = CreateNamedRange(.range("A1"))
  </code></pre>