need help to define a range using two variables

[dschmitt]

I have a problem defining a range using two variables.

Below is a sub (Test1) that uses the function JoinCell. The JoinCells function joins a range of cells in a semicolon delimited way (see code for the function below).

For better illustration I attached an Excel file containing an example and the macro.

Macro Test1 is working. Since there are 10 rows in Sheet1 it copies the range A1-C1 ten times to sheet2.

But this is not what I want to do. I want to join each row on sheet1 and copy it to sheet2. I put my derired result on sheet3 for illustration.

I attempted to obtain my desired result with sub Test2 (see code below). I could not finish it because I don't know how to write the code for the range.

I indicated the problem code in sub Test2 with ???????. Somehow I need to incorporate into this range the variable "n" for the row and the variable LastColumn for the number of columns. Or it needs to be done completely differently. Please somebody assist.

Sub Test1()

Dim n As Long, LastRow As Long

LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

    For n = 1 To LastRow
        Sheets("Sheet2").Cells(n, 1) = JoinCell(Sheets("Sheet1").Range("A1:C1"))
    Next n

End Sub

Function JoinCell(myRange As Range) As String
Dim rCell As Range
For Each rCell In myRange
    JoinCell = JoinCell & "; " & rCell
Next
    JoinCell = Mid(JoinCell, 2)
End Function

Sub Test2()

Dim n As Long, LastRow As Long, LastColumn As Long

LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

    For n = 1 To LastRow
        LastColumn = Sheets("Sheet1").Cells(n, Columns.Count).End(xlToLeft).Column 
        Sheets("Sheet2").Cells(n, 1) = JoinCell(Sheets("Sheet1").Range("??????"))
    Next n

End Sub

[dschmitt]

solved by romperstomper.

Sub Test2()
'
' Test2 Macro
'
Dim n As Long, LastRow As Long, LastColumn As Long

LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

    For n = 1 To LastRow
        LastColumn = Sheets("Sheet1").Cells(n, Columns.Count).End(xlToLeft).Column
        Sheets("Sheet2").Cells(n, 1) = JoinCell(Sheets("Sheet1").Cells(n, "A").Resize(, LastColumn))
    Next n

End Sub