Setting a Range

T

Tempy

Good day,

I am trying to set a named range automatically. The problem is that the
range varies daily so i thought of using "cntrl/shft *" to select the
range. I treid recording a macro but it gives me the row & column
numbers and i can not use that.
Can somebody help me with this please.

Thanks in advance

Les Stout

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
J

Jan Karel Pieterse

Hi Tempy,
I am trying to set a named range automatically. The problem is that the
range varies daily so i thought of using "cntrl/shft *" to select the
range. I treid recording a macro but it gives me the row & column
numbers and i can not use that.
Can somebody help me with this please.

You might consider defining a dynamic range:

Use e.g. this formula in the Refersto box of the name:

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

The formula will count all entries in Column A to determine the number of
rows and all entries on row 1 to determine the number of columns to use,
starting from cell A1.

If you use defined names a lot, consider downloading the
Name manager (by Charles Williams, Matthew Henson and
myself) from:

www.jkp-ads.com

or

www.bmsltd.ie/mvp

or from:

www.decisionmodels.com/downloads.htm

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
T

Tempy

Hello Jan, dankie vir jou help, maar ek het a problem want ek is nie a
programerde nie?

I have a problem of putting the code together, could you help me?

Les Stout

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
 
J

Jan Karel Pieterse

Hi Tempy,
I have a problem of putting the code together, could you help me?

What problem? what I meant to say was to make the name dynamic in Excel
itself, so you needn't use code to do that, in code you can simply
refer to that name.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com
 
T

Tom Ogilvy

Perhaps you are not familiar with Names.

Under the Insert Menu:

Insert=>Names=>Define

Name: List1
Refersto:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

Click the add button.

to refer to the range in your code

set rng = Range("List1")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top