I need to extend my range from my limited arrays.

Hi Experts,

I have a working code having its limitations. I have a range "A" column [{9;11;13;15;17;19;21;23;25;27}]). However, I wanted to extend this range until there are available values on the cell selected. If the value has extended until 100, I do not want to input it manually, I am seeking your expertise to have a range or steps that can range to 9-until value available, and 10-until value available for the "C" Column.

Let me know if you need other information. I am not an expert as I was able to develop this code with the help of our friends here. Thank you.

Sub Template()
   Dim R&
       R = Val(InputBox("Input row number you want to copy" & vbCrLf & "(Note: Start on row 30)", "Row to Copy from Schema"))
    If R > 29 Then
        Range("C1").Value = Sheets("schema").Range("C" & R).Value
        Range("C3").Value = Sheets("schema").Range("H" & R).Value
        [A5:A14] = Application.Index(Sheets("schema").Rows(R), , [{9;11;13;15;17;19;21;23;25;27}]) ' i need to expand from 9- until there is value
        [C5:C14] = Application.Index(Sheets("schema").Rows(R), , [{10;12;14;16;18;20;22;24;26;28}]) ' i need to extend from 10- unil there is value
        
'not using       [D16:D30] = Application.Index(Sheets("additional text").Rows(R), , [ROW(7:21)])
     
    Dim lc As Long, i As Long, ws As Worksheet, rng As Range
    With Sheets("additional text")
        lc = .Cells(R, .Columns.Count).End(xlToLeft).Column
        Set rng = .Range(.Cells(R, 7), .Cells(R, lc))
    End With
    
    For Each cell In rng
    cell.Value = Trim(cell)
    Next cell
    
    Sheets("psgc").Range("D16").Resize(rng.Columns.Count) = Application.Transpose(rng)
    End If
End Sub

Sub ClearData()
    [C1].Clear
    [A5:A14].Clear
    [C5:C14,D15:D50].Clear
    Range("D1").Select
End Sub