title: Is there any way to merge relative cells in excel using VBA?
body: <p>Okay, so, I use Excel to generate invoices for my business and VBA help me track my sales by automatically posting various details of invoices like Date, Invoice number, Buyer name and quantity.</p>
  <p>The problem is there's only one cell for date, invoice number and buyer but the quantity takes my multiple rows, and due to which I have to manually select and merge the other cells.</p>
  <p>Here's my code -</p>
  <pre><code> Sub registerdrums()
     Set ws1 = Worksheets(&quot;performa&quot;)
     Set ws2 = Worksheets(&quot;billing&quot;)
      drum = ws1.Range(&quot;r25&quot;).Value
  'if the item sold is drum
  If drum = &quot;WD&quot; Then
  'if the row which is next
  nextrow = ws2.Cells(Rows.Count, 4).End(xlUp).Row + 1
  ' sold items
  ws1.Range(&quot;p25:q45&quot;).Copy
  ws2.Cells(nextrow, 4).PasteSpecial Paste:=xlValues
  'date
  ws2.Cells(nextrow, 1).Value = ws1.Range(&quot;E8&quot;)
  'invoice number
  ws2.Cells(nextrow, 2).Value = ws1.Range(&quot;E7&quot;)
  'buyer code
  ws2.Cells(nextrow, 3).Value = ws1.Range(&quot;Q20&quot;)
  ws1.Activate
  Else: If drum &lt;&gt; &quot;WD&quot; Then ws1.Activate
  End If
  End Sub
  </code></pre>