Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic range without using OFFSET()
I've seen a number of posts which use the OFFSET() function to create
a dynamic range. This is neat but I thought I could do it better by using my own function (see below). However, I seem to be unable to plot this range on a chart in the same way that I could plot a dyn. range creating using the OFFSET function. Does anyone know why this is or whether there is a work-around? Thanks, Andrew Public Function DYNRANGEV(FirstCell As Range) As Range Set DYNRANGEV = FirstCell.Parent.Range(FirstCell.Address & ":" & _ FirstCell.End(xlDown).Address) End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic range without using OFFSET()
I think you are geting confused by a dynamic range as set up in an
Excel Worksheet and that set up in VB code. The former is a *string* preceded by an '=', and can only use worksheet functions. It will not recognise .End(). Your function can only be used within VB. Here is a way of resetting the range using VB based on your requirement :- '-------------------------------------- Sub ChangeNamedRange() Dim RangeFormula As String Dim RangeName As String '---------------------- RangeName = "TestRange" RangeFormula = "='" & ActiveSheet.Name & "'!" _ & ActiveCell.Address & ":" _ & ActiveCell.End(xlDown).Address '- add name to collection ActiveWorkbook.Names.Add Name:=RangeName, RefersTo:=RangeFormula '- test ActiveSheet.Range(RangeName).Select End Sub '-------------------------------------- Regards BrianB ================================== Regards BrianB ============================= (Andrew) wrote in message . com... I've seen a number of posts which use the OFFSET() function to create a dynamic range. This is neat but I thought I could do it better by using my own function (see below). However, I seem to be unable to plot this range on a chart in the same way that I could plot a dyn. range creating using the OFFSET function. Does anyone know why this is or whether there is a work-around? Thanks, Andrew Public Function DYNRANGEV(FirstCell As Range) As Range Set DYNRANGEV = FirstCell.Parent.Range(FirstCell.Address & ":" & _ FirstCell.End(xlDown).Address) End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range for Offset | Excel Worksheet Functions | |||
Dynamic Range in Offset Help Request | Excel Worksheet Functions | |||
Dynamic Range Using INDEX instead of Offset | Excel Discussion (Misc queries) | |||
Offset, Dynamic range, Countif | Excel Discussion (Misc queries) | |||
dynamic range / offset | Excel Worksheet Functions |