title: Transpose multiple columns to multiple rows with VBA
body: <p>This is the kind of transformation is what I am trying to perform.<br />
  For illustration I made this as table. Basically the first three columns should repeat for however many colors are available.<br />
  <a href="https://i.stack.imgur.com/52s7H.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/52s7H.jpg" alt="enter image description here" /></a></p>
  <p>I searched for similar questions but could not find when I want multiple columns to repeat.</p>
  <p>I found this code online</p>
  <pre class="lang-vb prettyprint-override"><code>Sub createData()
      Dim dSht As Worksheet
      Dim sSht As Worksheet
      Dim colCount As Long
      Dim endRow As Long
      Dim endRow2 As Long
       
      Set dSht = Sheets(&quot;Sheet1&quot;) 'Where the data sits
      Set sSht = Sheets(&quot;Sheet2&quot;) 'Where the transposed data goes
       
      sSht.Range(&quot;A2:C60000&quot;).ClearContents
      colCount = dSht.Range(&quot;A1&quot;).End(xlToRight).Column
       
       '// loops through all the columns extracting data where &quot;Thank&quot; isn't blank
      For i = 2 To colCount Step 2
          endRow = dSht.Cells(1, i).End(xlDown).Row
          For j = 2 To endRow
              If dSht.Cells(j, i) &lt;&gt; &quot;&quot; Then
                  endRow2 = sSht.Range(&quot;A50000&quot;).End(xlUp).Row + 1
                  sSht.Range(&quot;A&quot; &amp; endRow2) = dSht.Range(&quot;A&quot; &amp; j)
                  sSht.Range(&quot;B&quot; &amp; endRow2) = dSht.Cells(j, i)
                  sSht.Range(&quot;C&quot; &amp; endRow2) = dSht.Cells(j, i).Offset(0, 1)
              End If
          Next j
      Next i
  End Sub
  </code></pre>
  <p>I tried changing step 2 to 1 and j to start from 4.</p>
  <p>Another example with two varied sets:<br />
  <a href="https://i.stack.imgur.com/Fhr8f.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Fhr8f.jpg" alt="2 varied sets" /></a></p>
  <p><a href="https://i.stack.imgur.com/lUNSt.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/lUNSt.jpg" alt="enter image description here" /></a></p>