I would like to add a cell to an existing range in a macro. I am new to
programming ranges and need some direction on this.

Do you mean just make the range bigger?

If yes, you can use .resize() to resize the original range:

range("a1").resize(1,10)
refers to a range that is 1 row by 10 columns starting in A1.

Range("a1:B99").resize(1,10)
is the same as that other! 1 row by 10 columns starting in A1.

So if you wanted to resize an existing range, you could do:

dim myRng as Range
dim myNewRng as range
Set myrng = worksheets("sheet9999").range("c9:e44")

with myrng
set mynewrng = .resize(.rows.count+1,.columns.count+1)
end with

It added 1 to both the number of rows and the number of columns.

And you don't have to use a new variable if you don't want to:

with myrng
set myrng = .resize(.rows.count+1,.columns.count+1)
end with

=======
As an aside, there's a way to "move" to a different location based on that
original range. Take a look at .offset() in VBA's help when you have time.

Edd wrote:
>
> I would like to add a cell to an existing range in a macro. I am new to
> programming ranges and need some direction on this.

--

Dave Peterson

On May 27, 10:00 am, Edd <E...@discussions.microsoft.com> wrote:
> I would like to add a cell to an existing range in a macro. I am new to
> programming ranges and need some direction on this.

Set MyRange = Union(MyRange, Range("C1"))

adds C1 to a previously Set range (MyRange)

Ken Johnson