How to create a chart with the last row data automatically when closing the form?

Question

I’m making a VBA form with information about some municipalities (that are in Combobox) and their respective goals to be hit (which is like a direct formula in the spreadsheet).

In the form I insert the information I want (some data from the municipalities) and a specific column in the spreadsheet contains the formula that makes the percentage of the completed goal so far.

I would like to make, when closing the form, automatically generate me a graph with the percentage of the goal and the name of the municipality, but this information of the goal is not in the code but directly in the spreadsheet.

Private Sub CommandButton1_Click()
Dim range1 As Range
Dim objeto As Control

Sheets("REGISTRO").Select
If Range("A2").Value = "" Then
    Set range1 = Range("A2")
Else
    Set range1 = Range("A1").End(xlDown).Offset(1, 0)
End If

range1.Value = UserForm2.ComboBox1.Value
range1.Offset(0, 1).Value = UserForm2.TextBox1.Value
range1.Offset(0, 2).Value = UserForm2.TextBox2.Value
range1.Offset(0, 4).Value = UserForm2.TextBox3.Value
range1.Offset(0, 5).Value = UserForm2.TextBox4.Value
range1.Offset(0, 6).Value = UserForm2.TextBox5.Value
range1.Offset(0, 7).Value = UserForm2.TextBox6.Value

'criando gráfico
range1.Select
range1.Activate
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("REGISTRO!A2,REGISTRO!H2")

'fechando a aplicação
UserForm2.Hide

For Each objeto In UserForm2.Controls
    On Error Resume Next
    objeto.Value = ""
Next

The column of the goal percentage is J. I tried to record a macro to do this, but I wanted to make the graph be generated from the last line, IE, the last municipality registered in the form already return me the chart with the completed percentage until that time.

I believe the problem is in this line "Source", but I don’t understand how to do it the way I want.

Thank you in advance.

Answer 1

Ideally you would create another range after setting the range1 and using the new range (range_grafico). Whereas the last column (of data) will be column 1 + 9 or "J", since range1 will already be in column 1. This rage_grafico you would use as 'Source' of 'Activechart'

Dim range gráfico as Range
'(...)
Set range_grafico = Range(range1, range1.Offset(, 9))

But, as something quick, see if this can help. Change this code:

'criando gráfico
range1.Select
range1.Activate
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
ActiveChart.SetSourceData Source:=Range("REGISTRO!A2,REGISTRO!H2")

By this code:

'criando gráfico
Range(range1, range1.Offset(0, 9)).Select
ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select