X Axis graph is not to scale

So Ive got a column that alternates consistently in size, so I am trying to Graph it with the code below, but the X Axis isnt to scale.

lr = ThisWorkbook.Sheets("Analysis").Cells(Rows.count, 12).End(xlUp).row
    Range(Selection, Selection.End(xlDown)).Select
     ActiveCell.Range("A1,L1").Select
    ActiveCell.Offset(1, 10).Range("G1").Activate
    ActiveSheet.Shapes.AddChart.Select
      ActiveChart.SetSourceData Source:=Sheets("Analysis").Range("A1,L1").EntireColumn

Sorry, not sure why my comments didnt show up. So Ive got column 12 which varies every time i run my data. Im using the lr function to count the total number of rows in the column. Then graph it, but when i edit the graph it says, the x scale is going from row 1 to row 6564! Ive tried to use Range("A1:A" & lr) but it doesnt seem to be working. Let me know if you require additional information.

Ive also tried it this way

 lr = ThisWorkbook.Sheets("Analysis").Cells(Rows.count, 12).End(xlUp).row
    Range(Selection, Selection.End(xlDown)).Select
     ActiveCell.Range("A1:A" & lr, "L1:L" & lr).Select
    ActiveCell.Offset(1, 10).Range("G1").Activate
    ActiveSheet.Shapes.AddChart.Select
      ActiveChart.SetSourceData Source:=Sheets("Analysis").Range("A1:A" & lr, "L1:L" & lr).EntireColumn

But this just screws up everything

Solution

You are selecting and activating stuff but nothing ever happens with the selected ranges. You calculate the last row, but you don't use it for anything but selecting ranges. In the last line of code, you set the range to the entire column. Try using code without .Select. That would at least remove 4 rows of unnecessary code from your VBA and make the code easier to understand.

Read this blog post by Dick Kusleika about Select and Activate and when it is / is not required and why: http://dailydoseofexcel.com/archives/2004/04/27/beginning-vba-select-and-activate/