Charts.Add - Smart, But Oh So Dumb

[Spiggy Topes]

Here's the scenario, in Excel 2007 (of course)I have a sheet with 365
rows, 253 columns. I have a macro that's going to build a chart based
on SOME of those columns. My code says

Set oActChart = Charts.Add

Used to work just fine in Excel 2003, now it takes 2.5 minutes and
sucks up half a gig of memory, because it thinks I want to chart
everything on the data sheet. I can get around this by selecting a
small range on the data sheet, but I'd far prefer to be able to tell
Excel not to try to anticipate what I want to do. Is there any simple
way - other than that above - to do this?

 

[Peter T]

Maybe something like this

Sub test()
Dim rSource As Range, rCol As Range
Dim cht As Chart
Dim sr As Series

Set rSource = ActiveSheet.Range("A110")

Call getNewChart(cht)
cht.SetSourceData rSource

' add more series and format the chart, eg
' Set sr = cht.SeriesCollection.NewSeries
' etc

End Sub

Sub getNewChart(chtSht As Chart)
Dim rOrig As Range
Set rOrig = ActiveWindow.VisibleRange
Application.EnableEvents = False
With ActiveSheet
Application.Goto .Cells(rOrig.Row, .Cells.Columns.Count), True
Set chtSht = Charts.Add
End With
Application.Goto rOrig(1), True

chtSht.Activate
Application.EnableEvents = True
End Sub

point being, ensure the activecell is empty and not part of some other
larger current-region.

Regards,
Peter T