Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro needs updated Range name addresses
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro needs updated Range name addresses
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") -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro needs updated Range name addresses
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro needs updated Range name addresses
Revenue has brought this to us :
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel cells randomly don't get updated unless each cell is updated | Excel Discussion (Misc queries) | |||
RANGE addresses. Getting from other cells. | Excel Worksheet Functions | |||
Sort Data auto on cell range when updated | Excel Programming | |||
Select updated data from a range of columns | Excel Worksheet Functions | |||
Translate range name passed as string to a custom function to range addresses! | Excel Programming |