Assing variable row to pivot chart in macro

[Purenergy]

Good day all,

Would anyone know how I can make this work:

Sub PREPARATION_TDC_II()
'
' PREPARATION_TDC Macro
'
'
    Dim i As Long
 
 
    Sheets("AFVAL NO GRPS").Select
 
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    i = Selection.Rows.Count
 
 
    Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "AFVAL NO GRPS!R1C1:RiC34", Version:=xlPivotTableVersion10). _
        CreatePivotTable TableDestination:="Feuil28!R3C1", TableName:= _
        "Tableau croisé dynamique1", DefaultVersion:=xlPivotTableVersion10
    Sheets("Feuil28").Select
    Cells(3, 1).Select
End Sub

What I want to do is that the size of the source data range be adjusted to the data, no matter if it grows in rows as I will have every time more and more rows.

As you see, I used the variable i in the range of the pivot table but it does not work.

Thank you for your assistance.