ExcelBanter - 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