Make last column in vba code dynamic

[JoeRooney]

Hi,

I have the below code that works great, however i need to update the last column depending on the number of columns in the file.

I am not sure how I can make the last column dynamic in the below code.

    Dim i As Long
    Dim Lastrow As Long
    Lastrow = Range("B" & Rows.Count).End(xlUp).Row

    For i = 2 To Lastrow

        Range("A" & i).Formula = Range("B" & i) & ";" & Chr(34) & Replace(Join(Application.Index(Range("c" & i & ":CS" & i).Value, 1, 0), """;"""), """""", "")
       
    Next i

 

[JoeRooney]

Ok, how about

    Dim i As Long
    Dim Lastrow As Long, LastCol As Long
    Lastrow = Range("B" & Rows.Count).End(xlUp).Row
    LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
   
    For i = 2 To Lastrow

        Range("A" & i).Formula = Range("B" & i) & ";" & Chr(34) & Replace(Join(Application.Index(Range("c" & i, Cells(i, LastCol)).Value, 1, 0), """;"""), """""", "")
      
    Next i

Thank you almost perfect, it is finding the last column but not adding the last " if there is data in the last column, example it is appearing as "ABC instead of "ABC"

When the last column is blank it is appearing as
;"
instead of
;

Thanks

 

[Fluff]

How about

        Range("A" & i).Formula = Range("B" & i) & ";" & Chr(34) & Replace(Join(Application.Index(Range("c" & i, Cells(i, LastCol)).Value, 1, 0), """;""") & Chr(34), """""", "")

 

[JoeRooney]

Range("A" & i).Formula = Range("B" & i) & ";" & Chr(34) & Replace(Join(Application.Index(Range("c" & i, Cells(i, LastCol)).Value, 1, 0), """;""") & Chr(34), """""", "")

Sorry for the delay responding, works perfectly. Thank you

 

[Fluff]

You're welcome & thanks for the feedback.