Greetings,

This should be simple, however I can't seem to find a simple way to
accomplish this task. All my attempts have ended up in a lot of code. Simply
stated, I have a data table which has variable number of columns. The top
row of cells is a named range e.g TopRow with entries in every cell. The top
row always starts in F7. The table rows extend down 28 rows. There are many
cells in the table which don't have entries.

What I would like to do is create a new range e.g. TableRange which
describes the entire table including TopRow down 28 rows. Then I can work on
the TableRange. My attempts have basically ended up counting each cell left
to right and then down which seems clumsy. I can tell you that the bottom
row cannot be guaranteed to have values in any cell. I was trying a number
of combinations of resize and offset,however I was unable to make them work.

What ideas can you suggest?

Thank you in advance for your help!

Ray

On Sat, 31 Jan 2004 03:07:22 GMT, "Ray Batig"
wrote:

Greetings,

This should be simple, however I can't seem to find a simple way to
accomplish this task. All my attempts have ended up in a lot of code. Simply
stated, I have a data table which has variable number of columns. The top
row of cells is a named range e.g TopRow with entries in every cell. The top
row always starts in F7. The table rows extend down 28 rows. There are many
cells in the table which don't have entries.

What I would like to do is create a new range e.g. TableRange which
describes the entire table including TopRow down 28 rows. Then I can work on
the TableRange.

Do I understand you correctly; the range name TopRow contains only the
columns that you want to add to the new range?

If so, this is one way (Change Sheet2 to wherever the range actually
appears):

Sub CreateTableRange()

Dim l_TopRowStart As Long
Dim l_TopRowCount As Long

l_TopRowStart = Range("TopRow").Column

l_TopRowCount = Range("TopRow").Columns.Count

ActiveWorkbook.Names.Add Name:="TableRange", _
RefersToR1C1:= _
"=Sheet2!R7C" & CStr(l_TopRowStart) _
& ":R34C" _
& CStr(l_TopRowStart + l_TopRowCount - 1)

End Sub


---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

The first one got away from me.

Range("TopRow").Resize(28).Name = "TableRange"

Demo'd from the immediate window:

Range("F7").Resize(1,20).Name = "TopRow"
? range("TopRow").Address
$F$7:$Y$7
Range("TopRow").Resize(28).Name = "TableRange"
? Range("TableRange").Address
$F$7:$Y$34

--
Regards,
Tom Ogilvy

Ray Batig wrote in message
nk.net...
Greetings,

This should be simple, however I can't seem to find a simple way to
accomplish this task. All my attempts have ended up in a lot of code.
Simply
stated, I have a data table which has variable number of columns. The top
row of cells is a named range e.g TopRow with entries in every cell. The
top
row always starts in F7. The table rows extend down 28 rows. There are
many
cells in the table which don't have entries.

What I would like to do is create a new range e.g. TableRange which
describes the entire table including TopRow down 28 rows. Then I can work
on
the TableRange. My attempts have basically ended up counting each cell
left
to right and then down which seems clumsy. I can tell you that the bottom
row cannot be guaranteed to have values in any cell. I was trying a
number
of combinations of resize and offset,however I was unable to make them
work.

What ideas can you suggest?

Thank you in advance for your help!

Ray

Thank You Tom,

You make it sooooo simple.

Ray
Tom Ogilvy wrote in message
...
The first one got away from me.

Range("TopRow").Resize(28).Name = "TableRange"

Demo'd from the immediate window:

Range("F7").Resize(1,20).Name = "TopRow"
? range("TopRow").Address
$F$7:$Y$7
Range("TopRow").Resize(28).Name = "TableRange"
? Range("TableRange").Address
$F$7:$Y$34

--
Regards,
Tom Ogilvy

Ray Batig wrote in message
nk.net...
Greetings,

This should be simple, however I can't seem to find a simple way to
accomplish this task. All my attempts have ended up in a lot of code.
Simply
stated, I have a data table which has variable number of columns. The
top
row of cells is a named range e.g TopRow with entries in every cell. The
top
row always starts in F7. The table rows extend down 28 rows. There are
many
cells in the table which don't have entries.

What I would like to do is create a new range e.g. TableRange which
describes the entire table including TopRow down 28 rows. Then I can
work
on
the TableRange. My attempts have basically ended up counting each cell
left
to right and then down which seems clumsy. I can tell you that the
bottom
row cannot be guaranteed to have values in any cell. I was trying a
number
of combinations of resize and offset,however I was unable to make them
work.

What ideas can you suggest?

Thank you in advance for your help!

Ray

Range("TopRow").Resize(28).Name = "TableRange"


demo from the immediate window:


Ray Batig wrote in message
nk.net...
Greetings,

This should be simple, however I can't seem to find a simple way to
accomplish this task. All my attempts have ended up in a lot of code.
Simply
stated, I have a data table which has variable number of columns. The top
row of cells is a named range e.g TopRow with entries in every cell. The
top
row always starts in F7. The table rows extend down 28 rows. There are
many
cells in the table which don't have entries.

What I would like to do is create a new range e.g. TableRange which
describes the entire table including TopRow down 28 rows. Then I can work
on
the TableRange. My attempts have basically ended up counting each cell
left
to right and then down which seems clumsy. I can tell you that the bottom
row cannot be guaranteed to have values in any cell. I was trying a
number
of combinations of resize and offset,however I was unable to make them
work.

What ideas can you suggest?

Thank you in advance for your help!

Ray