need help to define a range using two variables

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.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.