Select range of data VBA

Hi All,

I am trying to select a range of data using VBA. I have months (Jan - Dec) from A10 to A21 and a range of data for each month. I want to select the data from A10 to the last col used in row 21. for example if the last col is H then A10:H21.

I used following code but doesn't work. Any idea how to fix it?

    mnthlylc = wsElecOtpt.Cells(10, Columns.Count).End(xlToLeft).Column
    mnthlylr = wsElecOtpt.Range(wsElecOtpt.Cells(21, mnthlylc)).End(xlUp).Row
    Set rng = .Range(.Cells(10, "A"), .Cells(mnthlylr, mnthlylc))

well I am plotting a chart on a userform using vba and trying to select the range of data I want to display on chart. it does work if I select rng=.range("A10:H21") but no of Cols are varies could be 3 could be 5 could be 2 so I try to define to select a range from A10 to the cell in row 21 and last col used.

since the whole code is too long I only posted the bit I need to change but if that is help here's the complete code;

If cmboChrt.Text = "" Then

    MsgBox "Please select a chart from dropdown list"
    Exit Sub
    
End If

If cmboChrt.Text = "Monthly Electricity Consumption" And Sheets("Energy Summary").Range("B10").Value = "" Then
    MsgBox ("Please upload electricity data to create a chart")
End If

If cmboChrt.Text = "Monthly Electricity Consumption" And Sheets("Energy Summary").Range("B10").Value <> "" Then

    '~~> Set the sheet where you have the charts data
    Set wsElecOtpt = Sheets("Energy Summary")
    mnthlylc = wsElecOtpt.Cells(9, Columns.Count).End(xlToLeft).Column
    mnthlylr = wsElecOtpt.Range(wsElecOtpt.Cells(21, mnthlylc)).End(xlUp).Row

    '~~> This is your charts range
    With wsElecOtpt
'            x = .Range(.Cells(10, mnthlylc), .Cells(mnthlylr, mnthlylc)).Address(False, False)
            Set rng = .Range(.Cells(9, "A"), .Cells(mnthlylr, mnthlylc))
     End With

    '~~> Delete the temp sheeet if it is there
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Sheets("TempOtpt").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    '~~> Add a new temp sheet
    Set wsTemp = ThisWorkbook.Sheets.Add

    With wsTemp
        '~~> Give it a name so that I can delete it as shown above
        .Name = "TempOtpt"

        '~~~> Add the chart
        Set oChrt = .ChartObjects.Add _
            (Left:=50, Width:=560, Top:=75, Height:=370)

        '~~> Set the chart's source data and type
        With oChrt.Chart
            .SetSourceData Source:=rng
            .ChartType = xlLine
            .ApplyDataLabels xlDataLabelsShowLabelAndPercent
            .HasLegend = False
            .HasTitle = True
            .ChartTitle.Text = "Monthly Electricity Consumption"
            .ChartStyle = 232
            'X Axis
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Months"
            'Y Axis
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "MWh"
            .Axes(xlValue, xlPrimary).DisplayUnit = xlThousands
            .Axes(xlValue, xlPrimary).DisplayUnitLabel.Delete
        End With
        
    End With

    '~~> Export the chart as bmp to the temp drive
    oChrt.Chart.Export Filename:=TempPath & "TempChart.bmp", Filtername:="Bmp"

    '~~> Load the image to the image control
    Me.Image1.Picture = LoadPicture(TempPath & "TempChart.bmp")

    '~~> Delete the temp sheet
    Application.DisplayAlerts = False
    wsTemp.Delete
    Application.DisplayAlerts = True

    '~~> Kill the temp file
    On Error Resume Next
    Kill TempPath & "TempChart.bmp"
    On Error GoTo 0
End If

I was able to figure it out. If anyone having same problem or interested I changed the code as follow

            x = .Range(.Cells(10, "B"), .Cells(21, mnthlylc)).Address(False, False)
            Set rng = .Range("A10:A21," & x)