create chart with column axis

[mp]

Hi
Is it possible to create a chart using for example columnB as X axis and
columnE as y axis?
(in other words specify which col goes on which axis)
I haven't found a way to do this with chart wizard.
I also have to put an If clause to skip blank rows
so something like pseudo code... If Len(cells (B,x).value > 0) then ...use
this row else skip this row while creating chart

trying to make a line chart to visually display some data which isn't
perfectly arranged on the worksheet
in my case column B is a date and col E is a price but there are
various blank rows scattered through the worksheet
I wanted to see the price movement over time
Thanks for any suggestions or push in the right direction
Mark

 

[merjet]

Yes. When you get to Step 2 of the Chart Wizard click on the Series
tab. Post again if you have questions about the input there.

If you have missing rows, the chart will contain gaps. If you don't
want gaps, copy the data elsewhere getting rid of the missing rows and
make the chart from the copied data.

 

[merjet]

You can also skip rows by highlighting multiple blocks of cells using
the Ctrl key, e.g. A1:B10 and A15:B20. I recommend doing this before
starting the Chart Wizard.

 

[mp]

Yes. When you get to Step 2 of the Chart Wizard click on the Series
tab. Post again if you have questions about the input there.

If you have missing rows, the chart will contain gaps. If you don't
want gaps, copy the data elsewhere getting rid of the missing rows and
make the chart from the copied data.

Thanks, the only way I could figure out how to do it was copy the sheet and
delete everything except the two cols i wanted and delete all empty rows and
headers. Couldn't figure out how to select the col for the x axis and the
col for the y axis.
I was hoping to find a way without duplicating the sheet data since I have
many sheets.
Didn't realize charts were so hard in excel, never tried them before.
Thanks for the suggestions

 

[Don Guillett]

Thanks, the only way I could figure out how to do it was copy the sheet and
delete everything except the two cols i wanted and delete all empty rows and
headers.  Couldn't figure out how to select the col for the x axis and the
col for the y axis.
I was hoping to find a way without duplicating the sheet data since I have
many sheets.
Didn't realize charts were so hard in excel, never tried them before.
Thanks for the suggestions

You could set up macros to do this automatically

 

[mp]

Thanks, the only way I could figure out how to do it was copy the sheet
and
delete everything except the two cols i wanted and delete all empty rows
and
headers. Couldn't figure out how to select the col for the x axis and the
col for the y axis.
I was hoping to find a way without duplicating the sheet data since I have
many sheets.
Didn't realize charts were so hard in excel, never tried them before.
Thanks for the suggestions

You could set up macros to do this automatically

true, I assume you mean the copying of sheets and deleting of rows?
If I could create the chart via code it would be even more straight forward.
maybe i'll try recording a simple macro and see if i can figure out the
objects required to compose a chart

 

[mp]

You could set up macros to do this automatically

true, I assume you mean the copying of sheets and deleting of rows?
If I could create the chart via code it would be even more straight
forward.
maybe i'll try recording a simple macro and see if i can figure out the
objects required to compose a chart

Ok, I wrote routines to copy the original sheet, delete unwanted rows and
columns, create the chart from the data on that temp sheet, put it on the
original sheet, but then when I delete the temp data sheet excel crashes,
since the chart was getting it's data from there. So the only way I can see
to do this is double the sheet count by not deleting the temp sheets. Not
the ideal but maybe only way i can make a chart from non-contiguous ranges
of data???

 

[merjet]

So the only way I can see

to do this is double the sheet count by not deleting the temp sheets. Not
the ideal but maybe only way i can make a chart from non-contiguous ranges
of data???

You can use non-contiguous ranges. However, if the empty rows aren't
the same each time you recreate the graph and you want to use VBA,
then the code needs to be flexible enough.

 

[mp]

You can use non-contiguous ranges. However, if the empty rows aren't
the same each time you recreate the graph and you want to use VBA,
then the code needs to be flexible enough.

can you give an example of using non-contiguous source range?
here's what i'm doing so far
this works after i've copied the sheet and pared it down to two cols with no
blank rows

Sub CreateChart(LastRow As Long)
Dim oRng As Range

Set oRng = ActiveSheet.Range("A1:B" & CStr(LastRow))
Dim sheetName As String
sheetName = ActiveSheet.Name

Dim dYmin As Double
dYmin = GetColumnMinimum("B")

Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=oRng, PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:= _
sheetName

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.text = ActiveSheet.Name
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.text = "Date"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.text = "Price"
.Axes(xlValue).MinimumScale = dYmin
End With
End Sub

 

[merjet]

can you give an example of using non-contiguous source range?

Sub Macro1()
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range( _
"E1:F12,E22:F32,H1:H12,H22:H32"), PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = _
"=(Sheet1!R2C5:R12C5,Sheet1!R22C5:R32C5)"
ActiveChart.SeriesCollection(2).XValues = _
"=(Sheet1!R2C5:R12C5,Sheet1!R22C5:R32C5)"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

 

[mp]

Sub Macro1()
Charts.Add ....
End With
End Sub

Thanks merjet, sorry just saw this. Will check out tonight