I have the following problem:
I have writen a macro that goes to the last row that contains data then
count up until it finds data in the first column (A). From this I have
the first row that contins data in the first column and the last row
that has data. I can now say that I have the C1Rx:C16Rx (the last
column with data always P).

What I need to do is take the info that is in C1Rx (its text) and name
the range.
From the macro recorder I get this

ActiveWorkbook.Names.Add Name:="test",
RefersToR1C1:="=Sheet4!R2C1:R8C4"

But for the life of me I cant figure out how to use this to ad the name
and refer to the range correctly...

Something like this

ActiveWorkbook.Names.Add Name:= Range("A" & StartDataRow),
RefersToR1C1:=" =C1R(MyStartDataRow):R(LastDataRow)C16

I would greatly appreciate any help....

Thanks

Maybe like this (note the reference to the sheet name):

Dim rngName As String
rngName = Cells(StartDataRow, 1).Text
ActiveWorkbook.Names.Add Name:=rngName, _
RefersToR1C1:="=Sheet4!R" & StartDataRow & "C1:R" _
& LastDataRow & "C16"

Hope this helps
Rowan

Chris Salcedo wrote:
I have the following problem:
I have writen a macro that goes to the last row that contains data then
count up until it finds data in the first column (A). From this I have
the first row that contins data in the first column and the last row
that has data. I can now say that I have the C1Rx:C16Rx (the last
column with data always P).

What I need to do is take the info that is in C1Rx (its text) and name
the range.
From the macro recorder I get this

ActiveWorkbook.Names.Add Name:="test",
RefersToR1C1:="=Sheet4!R2C1:R8C4"

But for the life of me I cant figure out how to use this to ad the name
and refer to the range correctly...

Something like this

ActiveWorkbook.Names.Add Name:= Range("A" & StartDataRow),
RefersToR1C1:=" =C1R(MyStartDataRow):R(LastDataRow)C16

I would greatly appreciate any help....

Thanks

This Works GREAT !!!!!!!

I have a question, does the sheet name have to be explicit?? Can it be
the active sheet ???

Try:

rngName = Cells(StartDataRow, 1).Text
ActiveWorkbook.Names.Add Name:=rngName, _
RefersToR1C1:="=" & ActiveSheet.Name & "!R" & _
StartDataRow & "C1:R" & LastDataRow & "C16"

Regards
Rowan

Chris Salcedo wrote:
This Works GREAT !!!!!!!

I have a question, does the sheet name have to be explicit?? Can it be
the active sheet ???

And if you are only going to be refering to the activesheet then more
simply:

Range(Cells(StartDataRow, 1), Cells(LastDataRow, 16)).Name _
= Cells(StartDataRow, 1).Value

Regards
Rowan

Chris Salcedo wrote:
This Works GREAT !!!!!!!

I have a question, does the sheet name have to be explicit?? Can it be
the active sheet ???

Hi chris,

It looks like you want the range named after the top left cell.
see if there is anything here you can use.

http://vbaexpress.com/forum/showthread.php?t=5569

Dave

Chris Salcedo Wrote:
I have the following problem:
I have writen a macro that goes to the last row that contains data
then
count up until it finds data in the first column (A). From this I have
the first row that contins data in the first column and the last row
that has data. I can now say that I have the C1Rx:C16Rx (the last
column with data always P).

What I need to do is take the info that is in C1Rx (its text) and name
the range.
From the macro recorder I get this

ActiveWorkbook.Names.Add Name:="test",
RefersToR1C1:="=Sheet4!R2C1:R8C4"

But for the life of me I cant figure out how to use this to ad the
name
and refer to the range correctly...

Something like this

ActiveWorkbook.Names.Add Name:= Range("A" & StartDataRow),
RefersToR1C1:=" =C1R(MyStartDataRow):R(LastDataRow)C16

I would greatly appreciate any help....

Thanks


--
Piranha
------------------------------------------------------------------------
Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435
View this thread: http://www.excelforum.com/showthread...hreadid=474649