Cannot Exclude weekends from XValues

**wildjester** · 08-06-2009, 03:32 PM

Hey,

Lets say I have two dates,

1. 8/3/2009
2. 8/10/2009

I have a function which goes through each date, and calls Weekday(currDate) to check if it is a weekend(ie. 1 or 7). I only write weekday dates to a row in my excel sheet. This works fine.

However, when I create a range out of these dates and assign it to XValues

ActiveChart.SeriesCollection(1).XValues = DateRange

The chart displays the weekend dates!

I created a simple loop, and put a breakpoint to check the dates the range:

Dim currRange As Range
    For Each currRange In DateRange        
    Next currRange

The range contained:
8/3/2009
8/4/2009
8/5/2009
8/6/2009
8/7/2009
8/10/2009

I have no idea why the chart includes weekends,
8/8/2009
8/9/2009

Any ideas???

**teylyn** · 08-06-2009, 10:53 PM

Hi, sounds as if your x axis is a time scale axis. You need to edit the Chart Options to set the x axis to Category instead.

A time scale axis can be used when you want to plot data points on the true representation of when in time they occurred, relative to each other, so Excel inserts missing dates on the axis. This can have its uses.

But if you really only want to plot the x markers shown in your data, you need to select the Category option for you x axis.

If you use an XY Scatter chart with the dates on the X axis, it will be treated like a date/time scale axis and missing dates will be interpolated. Change the chart type to a line chart instead and select the Category for the x axis to plot only the data points in your table.

**wildjester** · 08-07-2009, 05:43 PM

Hey,

Thanks for the response. I think you are correct, but I was not able to fix it.

  
   Set DateRange = Range(Sheets("Sheet1").Cells(3, 7), (Sheets("Sheet 1").Cells(3, lastDateCol)))

Now DateRange contains:
8/3/2009
8/4/2009
8/5/2009
8/6/2009
8/7/2009
8/10/2009

    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(1).Values = estTotalRange
    ActiveChart.SeriesCollection(1).XValues = DateRange
    ActiveChart.SeriesCollection(1).Name = "=""Est Total"""
    ActiveChart.SeriesCollection(2).Values = actualTotalRange
    ActiveChart.SeriesCollection(2).Name = "=""Actual"""
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Results"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = Name
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Dates"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y-Axis"
        End With
    ActiveChart.Parent.Name = "The Chart"

I tried changing the stuff in bold around to categories, etc... However it still plots Aug 8 and Aug 9

What am I missing here?

Thanks again!

**teylyn** · 08-07-2009, 06:29 PM

ohhh, impressive. I only do charts on foot, not with VBA (waaaayy over my head), so I don't have a clue how to do that programatically. Sorry.

I'm sure the VBA wizards here can help you. Your best bet would be Andy Pope. Check out his site and maybe also Jon Peltiers www.peltiertech.com, coz he does a lot with VBA and charts, too.

**Andy Pope** · 08-08-2009, 07:22 AM

Add the following line into your code

    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Name"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Dates"
        
' text rather than timeseries axis
        .Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale
        
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Y-Axis"
        End With