Select data concurrant for chart

always_confused

Board Regular
Joined
Feb 19, 2021
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
Hello,

I am trying to plot a bunch of time series via a macro I've written. When the column of data are NOT concurrent (like Range("A:A") and Range("C:C")), it works fine. However if I want to plot Range B s Range A (timestamps in A, data in B), then the graph comes out all messed up (all condensed like it's not using the timestamp data at all), and when I select the graph to see what ranges it's using, the timestamp range is not selected. Has anyone encountered this issue or maybe know how to solve this? Here's the sub:

VBA Code:
Sub make_plot(name As String, place As Range, data_col As Range, time_col As Range)
'plots data from sheet(name) in range place using data_col and time_col as source 

Application.ScreenUpdating = False

Dim chartObj_1 As ChartObject
Dim newChart As Chart
Dim lastrow As Long
lastrow = Sheets(name).Range("A" & Rows.Count).End(xlUp).Row
Set chartObj_1 = Sheets(name).ChartObjects.Add _
    (Left:=place.Left, Width:=place.Width, Top:=place.Top, Height:=place.Height)
Set newChart = chartObj_1.Chart

With newChart
    .HasTitle = True
    .ChartTitle.Text = data_col.Range("A1").Value
    .ChartType = xlXYScatter
    .HasLegend = False
    .SetSourceData Source:=Application.Union(time_col, data_col)
    .Axes(xlCategory).HasTitle = True
    .Axes(xlCategory).HasMajorGridlines = True
    .Axes(xlCategory).AxisTitle.Caption = "Time"
    .Axes(xlValue).TickLabels.NumberFormat = "0.000"
    .Axes(xlValue).MinimumScale = 8.4
    .Axes(xlValue).MaximumScale = 9
    With .SeriesCollection(1)
        .MarkerStyle = xlMarkerStyleCircle
        .MarkerSize = 2
    End With
End With
ActiveWorkbook.Save
Application.ScreenUpdating = True
End Sub


Here's how I' calling it
VBA Code:
lastrow = Sheets("MySheet").Range("A" & Rows.Count).End(xlUp).Row

Call make_plot("MySheet", Range("K2:S18"), _   'this one doesn't work correctly
Range("B1:B" & lastrow), Range("A1:A" & lastrow))

Call make_plot("MySheet", Range("K21:S37"), _  'but this one works fine
Range("C1:C" & lastrow), Range("A1:A" & lastrow))
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Watch MrExcel Video

Forum statistics

Threads
1,132,976
Messages
5,656,184
Members
418,288
Latest member
reba557

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top