CODEWITHSUNDEEP
excelvbadynamicexcel-charts
bishwarup990
bishwarup990

Reputation: 29

Chart from dynamic range

I was making a graph from a dynamic chart using VBA. The Chart is coming as expected, however I want the X axis to show X values from columns only that contains data. Here it is showing all the columns including those that are not having any data.

Below is my code:

Dim rng As Range
Dim cht As Object
Dim LstRw2 As Long
Dim N As Long
    N = Cells(1, 1).End(xlDown).Row
   Set rng = Range("E2:O" & N)
  Set cht = ActiveSheet.Shapes.AddChart
  cht.chart.SetSourceData Source:=rng
  cht.chart.ChartType = xlColumnClustered
    ActiveSheet.ChartObjects("Chart 1").chart.SeriesCollection(1).XValues = _
"='Sheet1'!$F$11:$O$11"
End Sub

As of now it takes the xvalues from another sheet however I want to use the current worksheet for the values

enter image description here

In the added picture 2021,2022,2023 should not be showing for x axis since they do not have any data.

Upvotes: 1

Views: 179

Answers (1)

FaneDuru
FaneDuru

Reputation: 42236

Try this code, please:

Sub MakeChart()
 Dim rng As Range, cht As ChartObject, LstRow As Long, i As Long
 Dim sh As Worksheet, lastCol As Long, lastC As Long, ColAddr As String
  Set sh = ActiveSheet      'use here your sheet
   LstRow = sh.Cells(Cells.Rows.count, 1).End(xlUp).row 'last row in A:A
   'Last column on the third row
   For i = 2 To LstRow
        lastC = sh.Cells(i, Cells.Columns.count).End(xlToLeft).column
        If lastC > lastCol Then lastCol = lastC
   Next i
   ColAddr = sh.Cells(1, lastCol).Address 'last column address (on first row)

   Set rng = sh.Range(sh.Range("E2"), sh.Cells(LstRow, lastCol))
   Set cht = ActiveSheet.ChartObjects.Add(left:=100, top:=80, width:=350, _
                                                                height:=250)

   cht.Chart.SetSourceData source:=rng
   cht.Chart.ChartType = xlColumnClustered
    sh.ChartObjects(sh.ChartObjects.count).Chart.SeriesCollection(1).XValues = _
                                            "='" & sh.Name & "'!$F$1:" & ColAddr
End Sub

I updated the code to use the longer row of the range, in order to set which values to be used from Chart.SeriesCollection(1).XValues...

Upvotes: 1

Related Questions