A vba chart where x-axis is not in numerical order

[saltire1963]

I am trying to create a chart from a 2 column range of data using VBA (XL2010), one where the x-axis values are not in numerical order.

For example, in range A2:B6, if I have no's 1 to 5 down column A and the no's 3, 2, 6, 1, 8 down column B, I would like to create a bar style chart that shows the values (column B) listed in order from high to low, but also show the x-axis with the corresponding number from column A. For example, the highest no. in column B is 8 and is the 5th no. in column A. I would like the chart to put the no's in correct order 8, 6, 3, 2, 1 but also the corresponding no's as the x-axis i.e. 5, 3, 1, 2, 4

I've tried copying the range A1:B6 to G1:H6 and sorting this new range in a macro which works fine, but when I try to use the same code in my own module it sorts Column H, but does not bring the corresponding no's in column G along with it. See code below.

Range("A1").Select
Sheet1.Sort.SortFields.Add Key:=ActiveCell.Offset(0, 1).Range("A2:A6"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With Sheet1.Sort
.SetRange ActiveCell.Offset(0, 1).Range("A1:B6")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

The other problem is when I create the chart using the macro values, it still makes the x-axis no's 1 to 5. I've seen that I need to create an additional dummy series of values that are all 0's and then link the make the correctly ordered numbers in Column G into the labels for the zero values, which works fine when creating the chart in excel, but I need to be able to do this in vba. So if I add 2 more columns of data, say column E2:E6 is no's 1 to 5 and Column F2:F5 are all zero's I have tried the code:

Set Cht = Sheet1.Shapes.AddChart(XlChartType:=xlColumnClustered, Left:=[B46].Left, Top:=[B46].Top, Width:=[B46:I46].Width, Height:=[B46:B64].Height).Chart
With Cht
.SetSourceData Source:=Range("E2:H6")
.SetElement (msoElementLegendNone)
.ChartGroups(1).GapWidth = 100
.SetElement (msoElementDataLabelOutSideEnd)
End With

Sorry, its a bit long winded and would like to send spread sheet as example, but not sure how to do this. Appreciate any help.

 

[Andrew Poulsom]

If A1:B6 are sorted by column B descending, this will create the Chart:

Sub CreateChart()
    Dim Chobj As ChartObject
    Set Chobj = ActiveSheet.ChartObjects.Add(Left:=[B46].Left, Top:=[B46].Top, Width:=[B46:I46].Width, Height:=[B46:B64].Height)
    With Chobj.Chart
        .ChartType = xlColumnClustered
        .SeriesCollection.NewSeries
        With .SeriesCollection(1)
            .Name = "='" & ActiveSheet.Name & "'!$B$1"
            .Values = "='" & ActiveSheet.Name & "'!$B$2:$B$6"
            .XValues = "='" & ActiveSheet.Name & "'!$A$2:$A$6"
        End With
    End With
End Sub

 

[saltire1963]

Thanks for swift reply, however it doesn't sort the data. Can you pls tell me how I insert the code window and I can send you what it is I am trying to do?

 

[saltire1963]

Here is my code

Dim Sheet1 As Worksheet
    Dim Selection1 As Range
    Dim Selection2 As Range
    Dim BBCht As Chart
    'Dim DDCht As Chart
    Set Sheet1 = ThisWorkbook.Worksheets("Sheet1")
    On Error Resume Next
    
    Application.ScreenUpdating = False
        
    'Clear previous charts and data
    'Columns("A:Z").ClearContents
    
    'Clear all previous charts
    Sheet1.ChartObjects.Delete
    'Change Row Headings
    With Range("A1")
        .Value = "AA"
        .Offset(, 1).Value = "BB"
        .Offset(, 6).Value = "CC"
        .Offset(, 7).Value = "DD"
    End With
    'Make the column headings Bold
    With Cells(1, 1).Resize(1, 10)
        .Font.Bold = True
        .Columns("A:J").ColumnWidth = 10
    End With
    
    'Centre line all of the cells
    With Range(Cells(1, 1), Cells(21, 10))
        .HorizontalAlignment = xlCenter
        .Font.Size = 11
    End With
    'Install no's 1 to 20 down column A
    Cells(2, 1).Value = 1
    Cells(3, 1).Value = 2
    Cells(4, 1).Value = 3
    
    Set Selection1 = Range(Cells(2, 1), Cells(4, 1))
    Set Selection2 = Range(Cells(2, 1), Cells(21, 1))
    Selection1.AutoFill Destination:=Selection2
    'Enter formula in Column B
    Range("B2:B21").Select
    With Selection
        .FormulaArray = "=INT(RAND()*100)"
        .Copy
        'PasteValues over top of formulas
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End With
    Range("A1").Select
    
    'Copy no's 1 to 20 to Column E and Column G
    With Range("A2:A21")
        .Copy Destination:=Range("E2")
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        .Copy Destination:=Range("G2")
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End With
    
    'Fill Column F with the value 0 to act as dummy data range
    With Range("F2:F21")
        .Value = "0"
    End With
        
    'Copy data in Column B to Column H
    Range("B2:B21").Copy Destination:=Range("H2")
    
    Application.CutCopyMode = False
    
    '---------------------------------------------------------------------------------
    
    'SORT data for 2nd chart
    
    'SORT by Column H
    Sheet1.Sort.SortFields.Clear
    
    Range("G1").Select
        Sheet1.Sort.SortFields.Add Key:=ActiveCell. _
            Offset(0, 1).Range("A2:A21"), SortOn:=xlSortOnValues, Order:=xlDescending, _
            DataOption:=xlSortNormal
        
        
    'ActiveCell.Range("A1:B21").Select
    'ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    'ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=ActiveCell. _
        'Offset(0, 1).Range("A1:A20"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        'DataOption:=xlSortNormal
    'With ActiveWorkbook.Worksheets("Sheet1").Sort
        '.SetRange ActiveCell.Offset(-1, 0).Range("A1:B21")
        '.Header = xlYes
        '.MatchCase = False
        '.Orientation = xlTopToBottom
        '.SortMethod = xlPinYin
        '.Apply
    'End With
        
        
        With ActiveWorkbook.Worksheets("Sheet1").Sort
            .SetRange ActiveCell.Offset(0, 1).Range("A1:B21")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
    '--------------------------------------------------------------------------------
    
    'CREATE CHARTS
    
    'Create Column Chart for data BB
    Set BBCht = Sheet1.Shapes.AddChart(XlChartType:=xlColumnClustered, Left:=[B25].Left, Top:=[B25].Top, Width:=[B25:I25].Width, Height:=[B25:B43].Height).Chart
        With BBCht
            .SetSourceData Source:=Range("B2:B21")
            .SetElement (msoElementLegendNone)
            .ChartGroups(1).GapWidth = 100
            .SetElement (msoElementDataLabelOutSideEnd)
        End With
    
    
    Dim DDCht As ChartObject
    Set DDCht = ActiveSheet.ChartObjects.Add(Left:=[B46].Left, Top:=[B46].Top, Width:=[B46:I46].Width, Height:=[B46:B64].Height)
    With DDCht.Chart
        .ChartType = xlColumnClustered
        .SeriesCollection.NewSeries
        With .SeriesCollection(1)
            .Name = "='" & ActiveSheet.Name & "'!$H$1"
            .Values = "='" & ActiveSheet.Name & "'!$H2:$H$21"
            .XValues = "='" & ActiveSheet.Name & "'!$G$2:$G$21"
        End With
    End With
    
    
    'Create Column Chart for data DD
    'Set DDCht = Sheet1.Shapes.AddChart(XlChartType:=xlColumnClustered, Left:=[B46].Left, Top:=[B46].Top, Width:=[B46:I46].Width, Height:=[B46:B64].Height).Chart
        'With DDCht
            '.SetSourceData Source:=Range("H2:H21")
            '.SetElement (msoElementLegendNone)
            '.ChartGroups(1).GapWidth = 100
            '.SetElement (msoElementDataLabelOutSideEnd)
        'End With
    
    '--------------------------------------------------------------------------------
    Application.ScreenUpdating = True

 

[Andrew Poulsom]

Don't you already have the code to sort by column B descending? You seem to have commented it out.

 

[saltire1963]

Thats correct, but it does not bring the cells from the previous column with it, they still remain in numerical order, whereas I am not looking at the column before as numbers in a sequence, rather I am looking at them as values that stay with the numbers in column B when column B is sorted.

 

[Andrew Poulsom]

Are you sure that you are sorting both columns A:B by column B? You should get this:

Excel 2010
	A	B
2	5	8
3	3	6
4	1	3
5	2	2
6	4	1
Sheet1