Addcing a cell to a non-contigious range

[Guest]

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]

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.

 

[Ken Johnson]

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