Hi:

I have a spreadsheet that contains a section I wish to import into Access. Because the
amount of data (specifically the number of rows) varies, I wish to give the range a name,
and I want to the name to refer to only those cells that contain data in column A. I know
how to SELECT the range, BUT I do not know how to make that selected range into a named
range, through programming.

If someone could help me with this I would much appreciate it.

Regards

John Baker

John,

Try a name with a RefersTo value of something like

=Sheet1!$A$1:OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$ A)-1,0)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John Baker" wrote in message
...
Hi:

I have a spreadsheet that contains a section I wish to import into Access.
Because the
amount of data (specifically the number of rows) varies, I wish to give
the range a name,
and I want to the name to refer to only those cells that contain data in
column A. I know
how to SELECT the range, BUT I do not know how to make that selected range
into a named
range, through programming.

If someone could help me with this I would much appreciate it.

Regards

John Baker

Hi,

By coincidence I posted a question invovling this earlier today.

One way would be to insert a named range in the excel sheet, called
DynaRange, say, and in the refers to box just add:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet!!$A:$A)-1)

This assumes you have a header label in cell A1, and that you don't
have any empty rows within the main range.



Cheers,

Graham


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

oops, slight typo in the previous response

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet!$A:$A)-1)


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Thanks

Can you give me the exat syntax -- im not to good on VBA.

Regards

John Baker

"Bob Phillips" wrote:

John,

Try a name with a RefersTo value of something like

=Sheet1!$A$1:OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A: $A)-1,0)

I believe there must be a command that loks like"

named.Range = ActiveCell()

I know thats not it, but is there something like that?


John Baker wrote:

Hi:

I have a spreadsheet that contains a section I wish to import into Access. Because the
amount of data (specifically the number of rows) varies, I wish to give the range a name,
and I want to the name to refer to only those cells that contain data in column A. I know
how to SELECT the range, BUT I do not know how to make that selected range into a named
range, through programming.

If someone could help me with this I would much appreciate it.

Regards

John Baker

Hi:

After a lot of digging, I found what I needed was a code set that looked like this:

Range("a2").Select
Selection.End(xlDown).Select
IROW = ActiveCell.Row
ICOLUMN = ActiveCell.Column
Cells(IROW, ICOLUMN).Select
'name the range transferdata

Cells(IROW, ICOLUMN).Name = "TRANSFERDATA"

This could help others with the same problem.

Regards

John Baker

John Baker wrote:

Hi:

I have a spreadsheet that contains a section I wish to import into Access. Because the
amount of data (specifically the number of rows) varies, I wish to give the range a name,
and I want to the name to refer to only those cells that contain data in column A. I know
how to SELECT the range, BUT I do not know how to make that selected range into a named
range, through programming.

If someone could help me with this I would much appreciate it.

Regards

John Baker