How do I name a range and then have that range be dynamic?

  • Thread starter Thread starter CellShocked
  • Start date Start date
C

CellShocked

I want to select the entire contents of a worksheet, but that data set
will change each time I open it as records will be added to it every day.

I want to always choose all records, and there will never be any
non-contiguous rows in the data or blank rows in it.

So I can make a direct call to it and always get all of it by way of
the named range.
 
If A1 is the header for example for the first column you can use

Range("A1").CurrentRegion.Select

The same as Ctrl *
 
By selecting any cell in the range you can use
selcetion.goto.currentregion and name it using code
 
myName:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

If only for use in VBA, you say 'Select', simply
ws.Range("A1").CurrentRegion.Select

Both the above assumes your first record starts in A1

Regards,
Peter T
 
Back
Top