General discussion

Locked

Excel Charts, how to auto select ranges

By asmaklad ·
I have a table that is filled by a query from the DB, it returns from 10 to 15 rows.
how can I create a chart that will handle the number of rows dynamically without the user having to define the range of values and lables ?

This conversation is currently closed to new comments.

2 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel Charts, how to auto select ranges

by DKlippert In reply to Excel Charts, how to auto ...

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

Collapse -

Excel Charts, how to auto select ranges

by asmaklad In reply to Excel Charts, how to auto ...

as for the 1st Option, i tried it but due to some reason, the chart options didn't accept the offset function.

the 2nd solution was OK and did work.

but I'm still looking for somthing like the first solution if possible.

Back to Windows Forum
2 total posts (Page 1 of 1)  

Related Discussions

Related Forums