Code reference changes

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

I have a workbook which is ever changing and wonder if there is a way to
have the code in VBA, that refers to a certain range, change just as a named
range might change if that range's size/reference changes. So in other
words, is there a method of showing a named range within the code, as you
would in a formula with a named range, instead of Range ("A1"), so that the
code will always work even if the range has expanded or changed?
Rob
 
Dim myRng as Range

with worksheets("sheet1")
set myrng = .range("a1",.cells(.rows.count,"A"))
myrng.name = "myName"
'do lots of stuff
.range("myName").clearcontents
'but if you assign the range in vba, you don't need the name:
myrng.clearcontents
end with

But you could also use a dynamic range name and just refer to that:

worksheets("sheet1").range("myEverGrowingRange").clearcontents

Debra Dalgleish has instructions at:
http://www.contextures.com/xlNames01.html#Dynamic
 
Thanks Dave,
Another great help.
Rob

Dave Peterson said:
Dim myRng as Range

with worksheets("sheet1")
set myrng = .range("a1",.cells(.rows.count,"A"))
myrng.name = "myName"
'do lots of stuff
.range("myName").clearcontents
'but if you assign the range in vba, you don't need the name:
myrng.clearcontents
end with

But you could also use a dynamic range name and just refer to that:

worksheets("sheet1").range("myEverGrowingRange").clearcontents

Debra Dalgleish has instructions at:
http://www.contextures.com/xlNames01.html#Dynamic
Click to expand...
 
Hi Dave, back again. I don't understand the
worksheets("sheet1").range("myEverGrowingRange").clearcontents bit. Why
clear contents?
Rob

rob nobel said:
Thanks Dave,
Another great help.
Rob

Dave Peterson said:
Dim myRng as Range

with worksheets("sheet1")
set myrng = .range("a1",.cells(.rows.count,"A"))
myrng.name = "myName"
'do lots of stuff
.range("myName").clearcontents
'but if you assign the range in vba, you don't need the name:
myrng.clearcontents
end with

But you could also use a dynamic range name and just refer to that:

worksheets("sheet1").range("myEverGrowingRange").clearcontents

Debra Dalgleish has instructions at:
http://www.contextures.com/xlNames01.html#Dynamic
Click to expand...
that
Click to expand...
 
You didn't say what you wanted to do with it. I just chose .clearcontents as an
example.



rob said:
Hi Dave, back again. I don't understand the
worksheets("sheet1").range("myEverGrowingRange").clearcontents bit. Why
clear contents?
Rob
Click to expand...
 
Back
Top