Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
create chart with column axis
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
create chart with column axis
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
create chart with column axis
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
create chart with column axis
"merjet" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
create chart with column axis
On Feb 13, 1:55*pm, "mp" wrote:
"merjet" wrote in message ... 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 You could set up macros to do this automatically |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
create chart with column axis
"Don Guillett" wrote in message ... On Feb 13, 1:55 pm, "mp" wrote: "merjet" wrote in message ... 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 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
create chart with column axis
"mp" wrote in message ... "Don Guillett" wrote in message ... On Feb 13, 1:55 pm, "mp" wrote: "merjet" wrote in message ... 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 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??? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
create chart with column axis
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
create chart with column axis
"merjet" wrote in message ... 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. 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 Whe=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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
create chart with column axis
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 Whe=xlLocationAsObject, Name:="Sheet1" With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With End Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
create chart with column axis
"merjet" wrote in message ... can you give an example of using non-contiguous source range? Sub Macro1() Charts.Add .... End With End Sub Thanks merjet, sorry just saw this. Will check out tonight |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
line chart using one column as x axis, another column as y axis? | Charts and Charting in Excel | |||
TO CREATE A LINE CHART WITH RATE IN Y AXIS & REAL TIME IN X AXIS | Charts and Charting in Excel | |||
how do i create a bar chart that has 2 y axis instead of one? | Charts and Charting in Excel | |||
How do I create more than two Y axis in a chart? | Charts and Charting in Excel | |||
How do I create a Line-Column on 2 Axis chart that allows more... | Charts and Charting in Excel |