Using VBA to create categorical axis on Bar graph

[elg0007]

I've got a macro that's searching a spreadsheet and pulling off data I want, and I want to put it into a bar graph with a categorical axis going vertical (Truck 1, Truck 2, etc.). Is there a way to either a) build arrays and then use them as the chart data with SetSourceData, or b) copy the information to a few random columns and use SetSourceData on those cells, so that I can get a categorical axis? Hope this makes sense.

Currently I have the document searching for my information, copying it to 2 random columns in the worksheet, then defining my ranges. But the categorical axis is coming up as 1,2,etc. :

Sheets("Competitive Position").Shapes("Chart 19").Select
Dim Range1 As Range
Dim Range2 As Range
Dim myrange As Range
Set Range1 = Sheets("Competitive Position").Range(Cells(2, 15), Cells(2 + NumCols, 15))
Set Range2 = Sheets("Competitive Position").Range(Cells(2, 16), Cells(2 + NumCols, 16))
Set myrange = Union(Range1, Range2)

ActiveChart.SetSourceData Source:=myrange


Any help would be greatly appreciated!

 

[Worf]

See if this helps:

Sub BarChart()
Dim sch As Shape
Set sch = ActiveSheet.Shapes.AddChart2(216, xlBarClustered)
With sch.Chart
    .SetSourceData Range("'Competitive Position'!$N$2:$P$5")
    .HasTitle = False
    .HasLegend = False
End With
With sch
    .Top = 5
    .Left = Range("g1").Left
    .Width = Range("g1:m1").Width
    .Height = Range("a1:a9").Height
End With
End Sub

 

[elg0007]

Alright I used some of that and changed some. I actually decided that a column graph is what I wanted, and I got the categories working. I've got one more question though, My macro is selecting the data for the chart fine, but I really want the bottom of the plot area (aka the x axis) to stay in a certain place because I have text in the spreadsheet that is in line with the chart. But depending on what data is selected, and how many characters are in the categories, the chart resizes every time I change something. Resizing the width is fine (when there are more or fewer data points) but I don't want it to change vertically. Here's what I'm trying and still can't get it to work:

Sheets("Competitive Position").Shapes("Chart 19").Select
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = _
Worksheets("Competitive Position").Range(Cells(2, 16), Cells(2 + NumCols - 1, 16))
ActiveChart.SeriesCollection(1).XValues = _
Worksheets("Competitive Position").Range(Cells(2, 15), Cells(2 + NumCols - 1, 15))
With ActiveChart.PlotArea
.Width = (75 + (31 * (NumCols - 1)))
.Top = -5
.Height = 492
.Left = 10
End With

Thank you for your help!

 

[Worf]

A chart or its plot area can be resized by code, using explicit point values or range references. This thread already has examples of that.
Do you need columns of different widths on this chart? I didn’t completely understand what the problem is.