How to Select NameRange in VBA?

[Dennis]

Using Office 2003

Need proper way to select and use an XL named Range in VBA.

SheetName = Ameritech
Range = AmerDataRng

Reason:

I believe that VBA code does not AutoUpdate the code when a new row or column is
added within a named range. I know that XL does "expand" the range.

Therefore, if I name the range in XL, all I need to do in VBA is use the RangeName in
the specific code? Am I correct or no?

What I want to do is select the range in VBA, then in VBA, add a Custom Filter to two
columns. Anything else I should consider?

TIA Dennis

 

[Bob Phillips]

Using Office 2003

Need proper way to select and use an XL named Range in VBA.

SheetName = Ameritech
Range = AmerDataRng

Range("AmerDataRng")


Reason:

I believe that VBA code does not AutoUpdate the code when a new row or column is
added within a named range. I know that XL does "expand" the range.

It depends how you create it. If you make it a dynamic range (that is one
that is dependent upon the n umber of rows/columns with data) then it does.
If you donn't, then it doesn't.

Therefore, if I name the range in XL, all I need to do in VBA is use the RangeName in
the specific code? Am I correct or no?

I fail to see why this is 'Therefore' so don't know what to say.

What I want to do is select the range in VBA, then in VBA, add a Custom Filter to two
columns. Anything else I should consider?

What does selecting the range have to do with putting a filet on two
columns?

 

[Dennis]

I'll take a stab myself.

Through trial and error, I found out that I could use:

Range("AmerDataRng").Select

Originally, I tried Range(AmerDataRng).Select and it failed.

In my previous coding days, variables were used i.e. AmerDataRng
not "AmerDataRng" because AmerDataRng would return a string and
"AmerDataRng" would return 'AmerDataRng'.

Confusing

 

[Dennis]

I wanted to:

1) Initialize the "updated" Range.
2) Reset the autofilter to include the updated range
3) Clear any custom-filter settings
4) then set custom filters on two columns

Am I doing things that are not necessary?

PS Did you mean Relative vs.. Absolute range?

How does one "create a dynamic range" in VBA?


TIA Bob

Dennis

 

[icestationzbra]

check this site

http://www.contextures.com/xlNames01.html#Dynamic

 

[Dennis]

Thank you!

Yours is a complete answer that works!

Dennis

 

[Bob Phillips]

Dennis,

One of the beauties of VBA is that you don't have to define it as a dynamic
range to dynamically set it (sic!), as VBA is dynamic by its nature. All you
need to do is to find the extent of the range and set accordingly. For
instance, if the range starts in row10, and you determine that it ends in
row 34, all in column C, then you can use

Range("C10:C34).Name = "dennis"

but it becomes better when you have a variable, say cLast with the last row
number in it, so we then use

Range("C10:C"&cLast).Name = "dennis"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dennis]

Thanks Bob!