title: Resize a named range in Excel via VBA
body: <p>I have a named range that I need to resize according to how many values are in the named range.  For example, if there are ten items the range should be $A$1:$I11, but I need it to shrink as well as expand.  Here is what I have so far:</p>
  
  <pre><code>Sub Generate()
  
  Dim wsFail as Worksheet
  Dim NextRow As Long, TotalRows As Long
  
  Set wsFail = Sheets("Failure Report")
  
   wsFail.Activate
      NextRow = Sheets("Failure Report").Range("A" &amp; Rows.Count).End(xlUp).Row
      TotalRows = Range("FailReportTable").Rows.Count
      Sheets("Failure Report").Range("A" &amp; TotalRows).Resize(TotalRows, 9).Value &amp; _
      = Range("FailReportTable").Columns("A:I").Value
  
  End Sub
  </code></pre>
  
  <h1>Updated Code (Still not working)</h1>
  
  <pre><code>With wsFail
      .Activate
          NextRow = .Cells(.Rows.Count, .Range("FailReportTable").Cells(1, 1).Column).End(xlUp).Row
          FirstCell = .Range("FailReportTable").Cells(1, 1).Address(False, False)
          LastCol = Split(.Range("FailReportTable").Cells(1, Range("FailReportTable").Columns.Count).Address(True, False), "$")(0)
          NewRange = FirstCell &amp; ":" &amp; LastCol &amp; NextRow
          .ListObjects("FailReportTable").Resize Range("NewRange")
  End With
  </code></pre>