General discussion

  • Creator
    Topic
  • #2091438

    Excel Charts, how to auto select ranges

    Locked

    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 ?

All Comments

  • Author
    Replies
    • #3880067

      Excel Charts, how to auto select ranges

      by dklippert ·

      In reply to 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

      • #3878444

        Excel Charts, how to auto select ranges

        by asmaklad ·

        In reply to Excel Charts, how to auto select ranges

        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.

Viewing 0 reply threads