Hello

Yesterday I got some help with code so that my code for inserting a graph looked for the last line of data that previous bit of code identified and not from line 3677 as the attached code currently shows. This was when the first part of the code was only selecting 1 column but now I need to graph two columns. The first part of code now correctly select the two columns.

So I recorded the macro for inserting the graph (after running the macro to select the two columns) and for that workbook it works fine and graphs correctly column E and P data just as I want. The problem is that recording the insert graph macro on that workbook puts in 3677 lines of data in the code. If I run the macro in another workbook that only has say 2000 lines of data the 3677 number is still used by the code.

what code do I need to replace the two references of 3677 in the graph code so that it always selects the last line of data that the first part of the macro code has selected?
Sub Macro1()
'
' Macro1 Macro
'
Dim lr As Long
Dim rngAllData As Range

lr = Worksheets("Sheet1").Cells(Rows.Count, "P").End(xlUp).Row

Set rngAllData = Union(Worksheets("Sheet1").Range("E17:E" & lr), Worksheets("Sheet1").Range("P17:P" & lr))
rngAllData.Select


ActiveSheet.Shapes.AddChart2(332, xlLineMarkers).Select
    ActiveChart.SetSourceData Source:=Range( _
        "Sheet1!$E$17:$E$3677,Sheet1!$P$17:$P$3677")
    ActiveSheet.Shapes("Chart 1").ScaleWidth 1.8520833333, msoFalse, _
        msoScaleFromBottomRight
    ActiveSheet.Shapes("Chart 1").ScaleHeight 1.0364585156, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 1").ScaleWidth 1.4308211474, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("Chart 1").ScaleHeight 0.9832499359, msoFalse, _
        msoScaleFromTopLeft
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartTitle.Text = "Subtwist"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Subtwist"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 8).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 8).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 14
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Spacing = 0
        .Strike = msoNoStrike
    End With
    ActiveChart.ChartArea.Select


'
End Sub
thanks DOMINIC