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

saltire1963

Board Regular
Joined
Aug 11, 2014
Messages
65
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.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
If A1:B6 are sorted by column B descending, this will create the Chart:

Code:
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

Board Regular
Joined
Aug 11, 2014
Messages
65
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

Board Regular
Joined
Aug 11, 2014
Messages
65
Here is my code

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

saltire1963

Board Regular
Joined
Aug 11, 2014
Messages
65
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Are you sure that you are sorting both columns A:B by column B? You should get this:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">5</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,126,928
Messages
5,621,639
Members
415,849
Latest member
PhoenixRising2015

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