How do I make references to cells in subroutines relative?

For example, if part of my subroutine is:

Range("A5:D33").Select

then I insert or delete cells in the spreadsheet, the references to these
cells in the macro stay the same, and are thus incorrect. They always have to
be updated manually. How can I make these change automatically when I modify
the spreadsheet?

Or is there a better way to reference cells in the worksheet?

Thanks
Atreides

Use named range instead of literal

Cheers
--
AP

"Atreides" <atreides1AThotmailD0Tcom a écrit dans le message de news:
...
How do I make references to cells in subroutines relative?

For example, if part of my subroutine is:

Range("A5:D33").Select

then I insert or delete cells in the spreadsheet, the references to these
cells in the macro stay the same, and are thus incorrect. They always have
to
be updated manually. How can I make these change automatically when I
modify
the spreadsheet?

Or is there a better way to reference cells in the worksheet?

Thanks
Atreides

Hi Ardus,

Sorry, I'm not sure what you mean by that. I've leart VBA by experiment
rather than instruction, so I'm not really familiar with the terms. How do I
use a named range?

Thanks
Peter

"Ardus Petus" wrote:

Use named range instead of literal

Cheers
--
AP

"Atreides" <atreides1AThotmailD0Tcom a écrit dans le message de news:
...
How do I make references to cells in subroutines relative?

For example, if part of my subroutine is:

Range("A5:D33").Select

then I insert or delete cells in the spreadsheet, the references to these
cells in the macro stay the same, and are thus incorrect. They always have
to
be updated manually. How can I make these change automatically when I
modify
the spreadsheet?

Or is there a better way to reference cells in the worksheet?

Thanks
Atreides

Select the range you want to name
Use menu InsertNameDefine
enter a valid name (eg: MyRange)
click OK

Now you can use:
Range("MyRange").Select
anywhere in your code.

Insertions and deletions will be reflected in your name definaition

Cheers,
--
AP


"Atreides" <atreides1AThotmailD0Tcom a écrit dans le message de news:
...
Hi Ardus,

Sorry, I'm not sure what you mean by that. I've leart VBA by experiment
rather than instruction, so I'm not really familiar with the terms. How do
I
use a named range?

Thanks
Peter

"Ardus Petus" wrote:

Use named range instead of literal

Cheers
--
AP

"Atreides" <atreides1AThotmailD0Tcom a écrit dans le message de news:
...
How do I make references to cells in subroutines relative?

For example, if part of my subroutine is:

Range("A5:D33").Select

then I insert or delete cells in the spreadsheet, the references to
these
cells in the macro stay the same, and are thus incorrect. They always
have
to
be updated manually. How can I make these change automatically when I
modify
the spreadsheet?

Or is there a better way to reference cells in the worksheet?

Thanks
Atreides

Thanks Ardus, that explains it well.

It seems that Names are even more useful than I previously thought!

Atreides

"Ardus Petus" wrote:

Select the range you want to name
Use menu InsertNameDefine
enter a valid name (eg: MyRange)
click OK

Now you can use:
Range("MyRange").Select
anywhere in your code.

Insertions and deletions will be reflected in your name definaition

Cheers,
--
AP


"Atreides" <atreides1AThotmailD0Tcom a écrit dans le message de news:
...
Hi Ardus,

Sorry, I'm not sure what you mean by that. I've leart VBA by experiment
rather than instruction, so I'm not really familiar with the terms. How do
I
use a named range?

Thanks
Peter

"Ardus Petus" wrote:

Use named range instead of literal

Cheers
--
AP

"Atreides" <atreides1AThotmailD0Tcom a écrit dans le message de news:
...
How do I make references to cells in subroutines relative?

For example, if part of my subroutine is:

Range("A5:D33").Select

then I insert or delete cells in the spreadsheet, the references to
these
cells in the macro stay the same, and are thus incorrect. They always
have
to
be updated manually. How can I make these change automatically when I
modify
the spreadsheet?

Or is there a better way to reference cells in the worksheet?

Thanks
Atreides