Hi:

This is a follow up to an earlier message, and I hope no one will be offended, but I have
been unable to implement the advice that was given.

Put simply, I want to make a named range where the definition of the range will vary
depending on how many records have been placed in the area. For the past couple of days I
have been messing with alternative ways to make it happen, and none work for one reason or
another. Either the protocol is wrong or the method is wrong or it comes up with a totally
off the wall answer!

The sheet is called timedata, and the area starts in A2, and goes down an unknown number
of rows, and includes cells from columns a through A-P (or an offset of 15).

The following has been the closest to working, but sill isn't acceptable to the VBA
compiler for some reason:

Sheets("timedata").Select
Range("a1").Select
Selection.End(xlDown).Select
'This defines the offset
irow = ActiveCell.Row
icolumn = ActiveCell.Column
icolumn=icolumn+15
HAVING TO THIS POINT, I TRIED A NUMBER OF THINGS INCLUDING"


Range(irow, icolumn).Name = "selectdata"

AND

range =timdata!A1:OFFSET(timedata!A1,irow(timedata!A:
A)+15,0).name

I don't know VBA very well and need some help with how I do this so it works!

I am sorry to have a duplicate (or almost duplicate) request in so short a time, but have
been messing with this for a couple of days with no joy at all, and am running out of
time.

Apologies and thanks in advance.

John Baker

set rng = Cells(rows.count,1).End(xlup)
Range(Range("A2"),rng).Resize(,16).Name = "selectdata"

--
Regards,
Tom Ogilvy

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

This is a follow up to an earlier message, and I hope no one will be
offended, but I have
been unable to implement the advice that was given.

Put simply, I want to make a named range where the definition of the range
will vary
depending on how many records have been placed in the area. For the past
couple of days I
have been messing with alternative ways to make it happen, and none work
for one reason or
another. Either the protocol is wrong or the method is wrong or it comes
up with a totally
off the wall answer!

The sheet is called timedata, and the area starts in A2, and goes down an
unknown number
of rows, and includes cells from columns a through A-P (or an offset of
15).

The following has been the closest to working, but sill isn't acceptable
to the VBA
compiler for some reason:

Sheets("timedata").Select
Range("a1").Select
Selection.End(xlDown).Select
'This defines the offset
irow = ActiveCell.Row
icolumn = ActiveCell.Column
icolumn=icolumn+15
HAVING TO THIS POINT, I TRIED A NUMBER OF THINGS INCLUDING"


Range(irow, icolumn).Name = "selectdata"

AND

range =timdata!A1:OFFSET(timedata!A1,irow(timedata!A:
A)+15,0).name

I don't know VBA very well and need some help with how I do this so it
works!

I am sorry to have a duplicate (or almost duplicate) request in so short a
time, but have
been messing with this for a couple of days with no joy at all, and am
running out of
time.

Apologies and thanks in advance.

John Baker

John,
Without taking the time to debug your problem, I think I can point you in
the right direction.

Range("A1").Offset(x,y) changes the location of the first cell of the
referenced range ("A1").
Range("A1").Resize(3,4) would create a range 3 rows by 4 columns starting at
A1.

Could this help??

Alex J



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

This is a follow up to an earlier message, and I hope no one will be
offended, but I have
been unable to implement the advice that was given.

Put simply, I want to make a named range where the definition of the range
will vary
depending on how many records have been placed in the area. For the past
couple of days I
have been messing with alternative ways to make it happen, and none work
for one reason or
another. Either the protocol is wrong or the method is wrong or it comes
up with a totally
off the wall answer!

The sheet is called timedata, and the area starts in A2, and goes down an
unknown number
of rows, and includes cells from columns a through A-P (or an offset of
15).

The following has been the closest to working, but sill isn't acceptable
to the VBA
compiler for some reason:

Sheets("timedata").Select
Range("a1").Select
Selection.End(xlDown).Select
'This defines the offset
irow = ActiveCell.Row
icolumn = ActiveCell.Column
icolumn=icolumn+15
HAVING TO THIS POINT, I TRIED A NUMBER OF THINGS INCLUDING"


Range(irow, icolumn).Name = "selectdata"

AND

range =timdata!A1:OFFSET(timedata!A1,irow(timedata!A:
A)+15,0).name

I don't know VBA very well and need some help with how I do this so it
works!

I am sorry to have a duplicate (or almost duplicate) request in so short a
time, but have
been messing with this for a couple of days with no joy at all, and am
running out of
time.

Apologies and thanks in advance.

John Baker

Tom:

A thousand thanks. It works, its simple, and I even understand it.

Best regards

John Baker



"Tom Ogilvy" wrote:

set rng = Cells(rows.count,1).End(xlup)
Range(Range("A2"),rng).Resize(,16).Name = "selectdata"

Alex:

Yes that would have helped a great deal. Its so useful I have saved it for future
reference.

Thanks very much

John Baker

"Alex J" wrote:

John,
Without taking the time to debug your problem, I think I can point you in
the right direction.

Range("A1").Offset(x,y) changes the location of the first cell of the
referenced range ("A1").
Range("A1").Resize(3,4) would create a range 3 rows by 4 columns starting at
A1.

Could this help??

Alex J



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

This is a follow up to an earlier message, and I hope no one will be
offended, but I have
been unable to implement the advice that was given.

Put simply, I want to make a named range where the definition of the range
will vary
depending on how many records have been placed in the area. For the past
couple of days I
have been messing with alternative ways to make it happen, and none work
for one reason or
another. Either the protocol is wrong or the method is wrong or it comes
up with a totally
off the wall answer!

The sheet is called timedata, and the area starts in A2, and goes down an
unknown number
of rows, and includes cells from columns a through A-P (or an offset of
15).

The following has been the closest to working, but sill isn't acceptable
to the VBA
compiler for some reason:

Sheets("timedata").Select
Range("a1").Select
Selection.End(xlDown).Select
'This defines the offset
irow = ActiveCell.Row
icolumn = ActiveCell.Column
icolumn=icolumn+15
HAVING TO THIS POINT, I TRIED A NUMBER OF THINGS INCLUDING"


Range(irow, icolumn).Name = "selectdata"

AND

range =timdata!A1:OFFSET(timedata!A1,irow(timedata!A:
A)+15,0).name

I don't know VBA very well and need some help with how I do this so it
works!

I am sorry to have a duplicate (or almost duplicate) request in so short a
time, but have
been messing with this for a couple of days with no joy at all, and am
running out of
time.

Apologies and thanks in advance.

John Baker