title: how to fill my empty last row using for loops VBA Excel
body: <p>Hello i tried to make a data table that consist a range of raw material with list of vendors like this image
  <a href="https://i.stack.imgur.com/qE7SN.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/qE7SN.jpg" alt="enter image description here" /></a>
  the data source is in another sheet
  <a href="https://i.stack.imgur.com/lDvq9.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/lDvq9.jpg" alt="enter image description here" /></a>
  the idea is to collect every unique content in supplier sheet and extract it to data table sheet (Row 1) so every raw material Category have a column that consist list of suppliers. but the problem i can't make the supplier name fill in every empty last row in each column and i can't have more than one supplier for each column. is there anyone can tell me what have i done wrong in my code?</p>
  <p>this is my code</p>
  <pre><code>Sub uniquevalues()
  
  Application.EnableEvents = False
  
  Dim arr As New Collection, a
  Dim arrS As New Collection, b
  Dim rngRawCategory As Variant
  Dim rngSupplier As Variant
  Dim lrow As Long
  
  rngSupplier = Range(&quot;C4:C1000&quot;) 'range in supplier sheet (Sheet3)
  rngRawCategory = Range(&quot;D4:D1000&quot;) 'range in supplier sheet (Sheet3)
  
  
  On Error Resume Next
  For Each a In rngRawCategory
      arr.Add a, a
  Next
  
  On Error Resume Next
  For Each b In rngSupplier
      arrS.Add b, b
  Next
  
  Sheet12.Range(&quot;B1:Z1000&quot;).ClearContents
  
  For i = 1 To arr.Count
      Sheet12.Cells(1, i + 1) = arr(i)
      For X = 1 To arrS.Count
          If Sheet3.Cells(X + 3, 4).Value = arr(i) Then
              lrow = Sheet12.Cells(Rows.Count, i).End(xlUp).Row + 1
              Sheet12.Cells(lrow, i + 1) = arrS(X)
          End If
      Next
  Next
  
  
  Application.EnableEvents = True
  
  End Sub
  </code></pre>