Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 2nd, 2012, 02:51 PM
Registered User
 
Join Date: Jan 2012
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Excel 2007 VBA Range Resize operation

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:

Code:
Option Explicit
Option Base 1

Dim rng 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"

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

End Sub
 
Old January 10th, 2012, 12:49 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

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)
 
Old January 17th, 2012, 11:05 AM
Registered User
 
Join Date: Sep 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Excel 2007 Range Resize operation

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
#NAME? error running Excel 2003 VBA in Excel 2007 steveburn Excel VBA 0 October 24th, 2009 08:47 AM
Help! Conversion Excel 2003 VBA codes to Excel 2007 sunny76 BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2 0 August 13th, 2009 05:38 AM
excel 2007 vba UDF DavidReese BOOK: Access 2007 VBA Programmer's Reference ISBN: 978-0-470-04703-3 1 February 24th, 2009 01:10 AM
VBA Print preview in Excel 2007 solnajeff Excel VBA 1 June 23rd, 2008 01:21 PM
Method Range - VBA - Excel teatimer Beginning VB 6 0 May 22nd, 2006 03:50 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.