Assuming there is something in row 1 for all the used columns, this should
work. James

Sub SetCols()
Dim LastCol As Integer, r As Range
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Set r = Range(Cells(1, 1), Cells(2, LastCol))
End Sub

wrote in message
...
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