Macro needs updated Range name addresses

  • Thread starter Thread starter Revenue
  • Start date Start date
R

Revenue

The range name "Current" needs to be a moving target so that when
something gets added at the bottom, the range updates itself. I tried
dynamic ranges with =offset and that did not pan out. This range is
used so that we can filter out rows with zeros in a certain column,
which is what the range "criteria" is for. The problem with this macro
right now is that I cannot properly assign the variable range1 to the
range name "Current" given that this range name does exist, it just
needs to be updated.

I have experimented with quite a few commands but right now look on
the 6th line down starting with
ActiveWorkbook.Names.Add Name:="Current" = Selection

Obviously that statement doesn't work or I would not be here typing
this right now.

Sub HideZeros()
Dim range1 As range
Application.Goto Reference:=range("start").Offset(1, 0)


Set range1 = range(ActiveCell.End(xlDown), ActiveCell.Offset(0, 25))
Application.Goto Reference:=range1

ActiveWorkbook.Names.Add Name:="Current" = Selection

range("Current").AdvancedFilter Action = xlFilterInPlace, _
CriteriaRange:=range("Criteria"), _
Unique:=False


End Sub
 
Dynamic ranges MUST be contiguous or they screw up.

When naming a range you need to 'set' its RefersTo to the range
address. So in your example...

ActiveWorkbook.Names.Add Name:="Current", RefersTo:=Selection.Address

HOWEVER!!!
I strongly urge you to NOT use global (workbook-level) names if
possible because these raise issues when copying/moving sheets to other
workbooks. It's ALWAYS better to use local (worksheet-level) names
whenever possible. To do so, prepend the defined name with the sheet
name wrapped in apostrophes followed by the exclamation character...

Name:="'" & ActiveSheet.Name & "'!Current"

...so for "Sheet1" the defined name is referred to as...

'Sheet1'!Current

...in formulas. To ref it in VBA...

Sheets("Sheet1").Names("Current")
 
ActiveWorkbook.Names.Add Name:="Current", RefersTo:=Selection.Address

Before I saw this, I actually got it to work without the .address
after selection. Is there a danger in not putting the .address in
there?
Or, is that the difference you meant is that way would cause a
workbook level names?

Thanks,


Bruce
 
Revenue has brought this to us :
Before I saw this, I actually got it to work without the .address
after selection. Is there a danger in not putting the .address in
there?
Or, is that the difference you meant is that way would cause a
workbook level names?

Thanks,


Bruce
Click to expand...

Selection returns an absolute address of its range object anyway and so
it's not really needed. HOWEVER, it would be 'good practice' to include
it as it helps document exactly what the code is doing.

This has nothing to do with workbook level names. any defined name that
excludes to syntax I posted for 'attaching' the name to a worksheet
will default to global scope. This is also considered 'good practice'
to NOT use global scope unless absolutely necessary. Just to
demonstrate the difference between global scope and local scope for
defined names...

Workbook1 has Sheet1 and Sheet2, both created from a template for
generating invoices.

Defined name ("InvNum") range on Sheet1 for Invoice# is local scope.
Defined name range ("InvNum") on Sheet2 for Invoice# is local scope.

Next invoice generated will also have a local scope defined name
("InvNum") for Invoice#, as will every sheet inserted using this same
template. You can't do this will global scope names because a 'name
conflict' exception will occur each time you try to insert another
sheet from that template. Having names use local scope avoids any 'name
conflict' exceptions. Since it's common to copy/move sheets from one
workbook to another, some discipline needs to be implemented when using
defined names so conflict exceptions don't screw up all your hard work!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top