Change Range Size

Posted by Richard Gordon on May 17, 2001 2:25 AM

Hi, maybe you folks can help me. I am writing a script and I want to change the size of a range. I have the name of the range, but I do not know its size. How can I:

1. Ask the range its size.
2. Add x to the range, so that it is larger.

I have seen functions like address, that will give size, but I dont know how to add to the address in A1B2 format and stuff it back again.

thanks
Richard

Posted by Dave Hawley on May 17, 2001 4:43 AM

Hi Richard

Name any range "MyRange" then run this code:

Sub Resize()
Dim iRows As Integer
Dim iCols As Integer

iRows = Range("Myrange").Rows.Count
iCols = Range("Myrange").Columns.Count

MsgBox "MyRange has " & iRows _
& " Rows and " & iCols _
& " Columns. It's Address is " & Range("MyRange").Address

Range("Myrange").Resize(iRows + 5, iCols + 5).Name = "MyRange"

iRows = Range("Myrange").Rows.Count
iCols = Range("Myrange").Columns.Count

MsgBox "MyRange has " & iRows _
& " Rows and " & iCols _
& " Columns. It's Address is " & Range("MyRange").Address

End Sub

Dave

OzGrid Business Applications