Bubble Chart :

Bubble chart represent Data in form of Bubble on X or Y Axis , every point of Bubble accept 3 argument X, Y and Size,

you can refer below table to create a bubble chart.

Name	X	Y	Size
A	70	40	1256
B	46	74	1433
C	67	94	1266
D	58	80	884
E	37	69	1286
F	65	67	1196
G	68	22	1218
H	10	84	577
I	56	14	1342
J	16	28	518
K	39	47	1609

Here is Code to create bubble chart on single click. Name the first cell of Table as “rngRange” to refer that in code.

Sub CreateBubbleChart()

    

    Dim chtBubble   As Chart

    Dim rngSource   As Range

    Dim rngCell     As Range

    Dim lngSeriesCount As Long

    Dim DataLabel       As Object

 

    lngSeriesCount = 1

    Set rngSource = Intersect(Range(“rngRange”).CurrentRegion, Range(“rngRange”).CurrentRegion.Offset(1))

    Set chtBubble = Sheet1.Shapes.AddChart(xlBubble, 200, 100, 700, 300).Chart

    

    

    With chtBubble

        For Each rngCell In rngSource.Rows

            .SeriesCollection.NewSeries

            .SeriesCollection(lngSeriesCount).Name = rngCell.Cells(1).Value

            .SeriesCollection(lngSeriesCount).XValues = rngCell.Cells(2).Value

            .SeriesCollection(lngSeriesCount).Values = rngCell.Cells(3).Value

            .SeriesCollection(lngSeriesCount).BubbleSizes = rngCell.Cells(4).Value

            .SeriesCollection(lngSeriesCount).ChartType = xlBubble3DEffect

            ‘working with DataLabels

            .SeriesCollection(lngSeriesCount).ApplyDataLabels

            Set DataLabel = .SeriesCollection(lngSeriesCount).Points(1).DataLabel

            DataLabel.Left = DataLabel.Left – 40

            DataLabel.ShowSeriesName = True

            lngSeriesCount = lngSeriesCount + 1

        Next rngCell

          .HasLegend = False

    End With

    

End Sub

Download  .xlsm from here

Thanks for Reading

Rajan.