Range Offset setup (follow Up)

  • Thread starter Thread starter John Baker
  • Start date Start date
J

John Baker

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"
 
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
 
Tom:

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

Best regards

John Baker
 
Alex:

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

Thanks very much

John Baker
 
Back
Top