title: Merge duplicate row using unique column
body: <p>Hi I'm trying to do a per subject and I'm trying to achieve 2 things.</p>
  <ol>
  <li>It ran successful until it got 'Run-time error 13 Type mismatch'. However when I stop the code, it manage to churn out the data successful. Am I right to say that this error occurs due to blank rows?</li>
  </ol>
  <p><a href="https://i.stack.imgur.com/tAMfv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/tAMfv.png" alt="Code as follow" /></a></p>
  <ol start="2">
  <li>Instead of using Column A as the unique column, I want to use column K as the unique column. However when I uses 'sh2.Range(&quot;K:K&quot;)' on line 11, I will get 'Run-time error 1004 You can't past this here because the copy area and paste area aren't the same size'. What I'm trying to achieve is using column K as the unique column and copy paste the entire sheet, after merging the duplicate, as it is but failed.</li>
  </ol>
  <p>Any advice is appreciated.</p>
  <p>Putting the source code here for anyone helping to work on.</p>
  <pre><code>Sub Merge_Duplicate()
  
      Dim sh1 As Worksheet, sh2 As Worksheet, i As Long, j As Long, f As Range, g As Range
  
      Application.ScreenUpdating = False
  
      Set sh1 = Worksheets(2)
      Set sh2 = Worksheets(3)
  
      sh2.Cells.ClearContents
      sh1.Rows(1).Copy sh2.Rows(1)
  
      For i = 2 To sh1.Range(&quot;A&quot; &amp; Rows.Count).End(xlUp).Row
          For j = 2 To sh1.Cells(i, Columns.Count).End(xlToLeft).Column
              If sh1.Cells(i, j) &lt;&gt; &quot;&quot; Then
                  Set f = sh2.Range(&quot;A:A&quot;).Find(sh1.Cells(i, &quot;A&quot;), , xlValues, xlWhole)
                  If Not f Is Nothing Then
                      Set g = sh2.Rows(1).Find(sh1.Cells(1, j), , xlValues, xlWhole)
                      If Not g Is Nothing Then
                          sh2.Cells(f.Row, g.Column) = sh1.Cells(i, j)
                      End If
                  Else
                      sh1.Rows(i).Copy sh2.Range(&quot;A&quot; &amp; sh2.Range(&quot;A&quot; &amp; Rows.Count).End(xlUp).Row + 1)
                  End If
              End If
          Next
      Next
  
  End Sub
  </code></pre>
  <p><a href="https://i.stack.imgur.com/K5miE.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/K5miE.jpg" alt="enter image description here" /></a></p>