I have a column in an Excel sheet containing data in this format: aaa,bbbb,ccc,dd,eeee,... each String is separated by a comma ","
I have created macro which splits the data in the column A and each String is separately inserted in a new cell for each row as shown in the screenshot.
Now I want to count how many used cells after the column B and according to that number repeat the value in the column B in a separate row and add to each row the next value in column C, D, E,...
At the end, the sheet 2 will look like this:
I have created a solution :
For i = 1 To 3
ActiveWorkbook.Sheets(2).Cells(i, 1).Value = ActiveWorkbook.Sheets(1).Range("B1").Value
ActiveWorkbook.Sheets(2).Cells(i, 2).Value = ActiveWorkbook.Sheets(1).Cells(1, i + 2).Value
Next i
But it works only when the column A has only one row. I have tried with different logic using Loops but it still doesn't get me the right result. I have hundreds of rows and it would be time consuming to do it manually. Any suggestion please. Thank you very much.