Resize Named Range VBA

Question

VBA Excel 2016

I'm trying to dynamically resize a range if the number of columns is less for that range after some code is executed. Referenced the MS files and various online examples with no luck. https://msdn.microsoft.com/en-us/library/office/ff193274.aspx

I can achieve the correct resize only if I do something like "testRange.Resize Range("A1:G1"). However I am looking for something along the lines of:

Sub Test_Range()

  Dim Test As Worksheet
  Set Test = Worksheets("test")

  Dim testTable As Range
  Set testTable = Test.Range("testTable[]")

  Dim testTableWidth, testNumbersWidth As Integer
  Set testTableWidth = testTable.Columns.Count

  'Some code
  'testNumbersWith is defined here

  If testNumbersWidth < testTableWidth Then
    testTable.Resize(, testNumbersWidth)
  End If

End Sub

Answer 1

The method to resize a "Named Range" is different if it is a "normal" named range or a "Table" (ListObject) range. Your title refers to the first case but from your code it seems you are working with a table.

For the case of a named range, you change the scope like this (i.e. to change the number of columns):

With ThisWorkbook.Names.Item("testTable")
    .RefersTo = .RefersToRange.Resize(, newColumnsCount)
End With

For the case of a Table (ListObject), which seems to be your case, you can change the number of columns like this:

  Dim testTable As ListObject             ' <-- Declare as ListObject
  Set testTable = Test.ListObjects("testTable")
  ' Or Set testTable = Test.Range("testTable")

  'Some code
  ' ....

   testTable.Resize testTable.Range.Resize(, newColumnsCount) ' <-- resize number of cols