Setting a Range

[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!

 

[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

 

[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!

 

[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

 

[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")

 

[Jan Karel Pieterse]

Hi Tom,

Perhaps you are not familiar with Names.

Thanks for elaborating.

Regards,

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

 

[Tempy]

Thanks Tom,

I really appreciate the help from all of you.

Les Stout

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