title: Loop through rows and columns Excel Macro VBA
body: <p>I have a column in an Excel sheet containing data in this format: <em>aaa,bbbb,ccc,dd,eeee,...</em> each String is separated by a comma ","</p>
  
  <p><a href="https://i.stack.imgur.com/Bj5M3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Bj5M3.png" alt="enter image description here"></a></p>
  
  <p>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.</p>
  
  <p>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,... </p>
  
  <p>At the end, the sheet 2 will look like this:</p>
  
  <p><a href="https://i.stack.imgur.com/MTyoD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/MTyoD.png" alt="enter image description here"></a></p>
  
  <p>I have created a solution :</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>