I'm trying to resize an existing named range by adding some rows and want to keep the range name the same. I'm using the example in the book "Excel 2007 VBA, Programmer's Reference" (page 104) which uses Resize and Name. When I print the row and column count before and after the resize, they show the same values. Is there a bug in "resize" or is the example in the book wrong? What is the best way to resize an existing named range while keeping the range name the same?

Code used to show example:

How are you resizing the Range and how are you printing

Cheers
Shasur

__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

I don't know whether it's a bug or not but I found that the rng variable remains set to the original range. If you assign TestRange1 to another variable after the resize operation, the second variable will be correct.

Try this:

Option Explicit
Option Base 1

Dim rng As Range, rng2 As Range
Dim wkb As Workbook
Dim wks As Worksheet
Sub RangeTest()

Set wkb = Workbooks(ActiveWorkbook.Name)
Set wks = wkb.Sheets("TestSheet1")
Set rng = wks.Range("TestRng1")

Debug.Print "Before resize:"
Debug.Print "#rows, #cols = ", rng.Rows.Count, rng.Columns.Count

rng.Resize(20).Name = "TestRng1"
Set rng2 = wks.Range("TestRng1")
Debug.Print "After resize:"
Debug.Print "First range: #rows, #cols = ", rng.Rows.Count, rng.Columns.Count
Debug.Print "Seconf range: #rows, #cols = ", rng2.Rows.Count, rng2.Columns.Count

End Sub