Working with the range object
 

Hi Everyone,

I am setting up a 'report' of sorts in Excel (97), and I cannot
remember or find how to calculate the range when I have to add a
variable number of columns. I dont actually want to make a named
range, simply refer to a range in VBA.

For example:
dim r as Range
set r = Worksheets(ActiveSheet.Name).Range("A1:E2")

that bit works fine so far, but the report has some unknown number of
columns that will be added to the right of these cells, and in order
to set the formatting correctly for the right 'look' I need to be able
to add n columns to the right of E2. Can anyone point me in the right
direction? Its probably something really simple but I just cant
remember it or seem to dig it up.

Thanks in advance

The Frog

Working with the range object
 

First, you could have used:

dim r as Range
set r = ActiveSheet.Range("A1:E2")

But if your cells in row 1 have something in them, you could use something like:

Dim R as range
Dim LastCol as Long
with Activesheet
lastcol = .cells(1,.columns.count).end(xltoleft).column
set r = .range("A1",.cells(2,lastcol))
end with


wrote:

Hi Everyone,

I am setting up a 'report' of sorts in Excel (97), and I cannot
remember or find how to calculate the range when I have to add a
variable number of columns. I dont actually want to make a named
range, simply refer to a range in VBA.

For example:
dim r as Range
set r = Worksheets(ActiveSheet.Name).Range("A1:E2")

that bit works fine so far, but the report has some unknown number of
columns that will be added to the right of these cells, and in order
to set the formatting correctly for the right 'look' I need to be able
to add n columns to the right of E2. Can anyone point me in the right
direction? Its probably something really simple but I just cant
remember it or seem to dig it up.

Thanks in advance

The Frog

--

Dave Peterson

Working with the range object
 

Thankyou fellas, much appreciated. You know, when you see the answers
that you just want to smack yourself in the head and DOH! My mental
block is now repaired, thankyou both so much.

Cheers

The Frog