Range Question

  • Thread starter Thread starter Ray Batig
  • Start date Start date
R

Ray Batig

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
 
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.
Click to expand...
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.
Click to expand...

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
 
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
 
Thank You Tom,

You make it sooooo simple.

Ray
Tom Ogilvy said:
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

work
Click to expand...
 
Back
Top