title: Defining the end of the Range with last cell with a value instead of the row number
body: <p>I am using with two different data dumps which are saved in OPL_Dump and OPL_DUMP_2 sheets.</p>
  <p>The code i am trying to improve (shared below), finds the data in one of the dumps' and copies and pastes as a new parameter as addition to the same corresponding value it sees for the other dump.</p>
  <p>The length of both the data dumps varies on a daily basis and i am trying to make my code a bit more robust instead of manually amending the length of the range every time.</p>
  <p>For this, i defined N and L instead of fixed numbers' of last rows but it it is not working.</p>
  <p>Can someone help please ?</p>
  <pre><code>Sub Merging_Both_Dumps_for_Product_Type()
  
  Dim out() As String
  
  'Dim out2() As String
  
  L As Long
  L = ThisWorkbook.Sheets(&quot;OPL_DUMP_2&quot;).Select.Cells(Rows.Count, &quot;B&quot;).End(xlUp).Row
  
  ThisWorkbook.Sheets(&quot;OPL_DUMP_2&quot;).Select
  
  keyarray = Range(&quot;F&quot; &amp; 2 &amp; &quot;:F&quot; &amp; L)
  
  valuearray = Range(&quot;J&quot; &amp; 2 &amp; &quot;:J&quot; &amp; L)
  
  N As Long
  
  N = ThisWorkbook.Sheets(&quot;OPL_DUMP&quot;).Select.Cells(Rows.Count, &quot;B&quot;).End(xlUp).Row
  
  ReDim out(N, 0)
  
  
  For j = 2 To N
    ind = Index(keyarray, ThisWorkbook.Sheets(&quot;OPL_DUMP&quot;).Cells(j, 2).Value)
    out(j - 2, 0) = valuearray(ind, 1)
  
  Next j
  
  'ReDim out2(1, 0)
  'out2(1, 0) = &quot;test&quot;
  
  'ThisWorkbook.Sheets(&quot;OPL_DUMP&quot;).Range(&quot;AD2:AD3&quot;) = out2()
  
  ThisWorkbook.Sheets(&quot;OPL_DUMP&quot;).Range(&quot;AC&quot; &amp; 2 &amp; &quot;:AC&quot; &amp; N) = out
  
  
  End Sub
  </code></pre>