need to get Range from FAQ into a RangeName somehow

[DougP]

Using this Great FAQ by tomreid faq707-2112

I can select the range but I need to make a chart from that.
The chart sample I got has the Range is hard coded.
Each time I run this Macro the range of cells will be different.
How can I put this into a Range name or something so make chart thingy can see the same reange?

    'this gets the current cells and highlights them
    Call FindUsedRange  'tomreid FAQ function
    'name the range
    Dim PlotRange As Excel.Range
    
    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:G20"), _
        PlotBy:=xlColumns   [COLOR=red]'<<<< need to put the range from above in here where A1:G20 is[/color]
    ActiveChart.Location Where:=xlLocationAsNewSheet
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Average (X Bar Chart)"
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

DougP, MCP, A+

 

[PHV]

Call FindUsedRange
strRange = Selection.Address(False, False)
...
Source:=Sheets("Sheet1").Range(strRange), _

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886