Excel Charts, how to auto select ranges

Here are a couple of ways to do this.
Go to Insert>Name>Define and create a function named for the first series of data (i.e. ?Date?)
=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$200))
Create another function for the next series (?Sales?)
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$200))
Use these names in formulas or to create charts. (=Sum(Sales))
I got this suggestion from:
http://www.cpearson.com/excel/excelF.htm

Here?s a macro that redefines the ranges and creates a chart. I?m sorry I don?t recall where I got this one.
Sub CreateChart()
' Select the cell in the upper-left corner of the chart.
Range("c4").Select
' Select the current range of data. This line of code assumes that
' the current region of cells is contiguous - without empty rows
' or columns.
Selection.CurrentRegion.Select
' Assign the address of the selected range of cells to a variable.
myrange = Selection.Address
' Assign the name of the active sheet to a variable. This line is ' used in order to allow a chart to be created on a separate chart
' sheet.
mysheetname = ActiveSheet.Name
' Add a chart to the active sheet.
ActiveSheet.ChartObjects.Add(125.25, 60, 301.5, 155.25).Select
' To create a chart on a separate chart sheet, remark out the
' previous line, and substitute the next line for the one above.
' Charts.Add
Application.CutCopyMode = False
' This line can best be written by recording a macro, and
' modifying the code generated by the Microsoft Excel Macro
' recorder.
ActiveChart.ChartWizard _
Source:=Sheets(mysheetname).Range(myrange), _
Gallery:=xlLine, Format:=4, PlotBy:=xlRows, _
CategoryLabels:=1, SeriesLabels:=1, HasLegend:=1, _
Title:="", CategoryTitle:="",_
ValueTitle:="", ExtraTitle:=""
End Sub

Good Luck