Chart Problems

[CrispyAsian]

Hello all,

I'm having trouble with a chart that is being built within my macro. It builds the chart just fine, but the two sets of data I want on the chart are 2 different columns and when the macro is done running instead of B25:B(LastRow) and F25:F(LastRow) being selected it has B25:B(LastRow) and C25:F(LastRow). Can someone tell me what I'm doing wrong?

Dim myWorksheet As Worksheet
Dim mySourceData As Range
Dim myChart As Chart
Dim myChartDestination As Range
Dim rng1 As Range, rng2 As Range
Dim Lastrow As Long
    
Lastrow = Range("A" & Rows.Count).End(xlUp).Row + 1



Set myWorksheet = ThisWorkbook.Worksheets("Overall Percentages")


Set rng1 = Range("B25:B" & Lastrow - 1)
Set rng2 = Range("F25:F" & Lastrow - 1)


With myWorksheet
    Set mySourceData = .Range(rng1, rng2)
    Set myChartDestination = .Range("A2:L23")
    Set myChart = .Shapes.AddChart(XlChartType:=xlColumnClustered, Left:=myChartDestination.Cells(1).Left, Top:=myChartDestination.Cells(1).Top, Width:=myChartDestination.Width, Height:=myChartDestination.Height).Chart
End With
    
myChart.SetSourceData Source:=mySourceData

P.S. I have to have the LastRow be +1 for other parts of the macro, that's why they have a -1 just to avoid confusion.

 

[=ODIN=]

Give this a try:

Dim myWorksheet As Worksheet
Dim mySourceData As Range
Dim myChart As Chart
Dim myChartDestination As Range
Dim rng1 As Range, rng2 As Range
Dim Lastrow As Long
    
Lastrow = Range("A" & Rows.Count).End(xlUp).Row + 1






Set myWorksheet = ThisWorkbook.Worksheets("Overall Percentages")






Set rng1 = Range("B25:B" & Lastrow - 1)
Set rng2 = Range("F25:F" & Lastrow - 1)


With myWorksheet




    Set myChartDestination = .Range("A2:L23")
    Set myChart = .Shapes.AddChart(XlChartType:=xlColumnClustered, Left:=myChartDestination.Cells(1).Left, Top:=myChartDestination.Cells(1).Top, Width:=myChartDestination.Width, Height:=myChartDestination.Height).Chart


    myChart.SetSourceData Source:=.Range(rng1.Address & "," & rng2.Address)
    
End With

 

[CrispyAsian]

What a simple and easy fix. I almost feel foolish for not thinking of it. Thank you so much, worked perfect.